問題描述

我得到了以下形式的有序 Microsoft Server 表:

I got an ordered Microsoft Server table of the following form:

    Name   Product
  ------------------
1 | Mayer  Product_1
2 | Mayer  Product_1
3 | Mayer  Product_2

我想得到以下結果:

    Name   Purchase_1 Purchase_2 Purchase_3
  -----------------------------------------
1 | Mayer  Product_1  Product_1  Product_2

代碼必須適用于任意長度的購買和名稱，這意味著我不知道這些信息.

The code has to work for an arbitrary length of purchases and names, meaning I don't know these information upfront.

推薦答案

Dynamic PIVOT 是你的朋友 :

現場演示

CREATE TABLE #mytable(
   Name  VARCHAR(80) NOT NULL 
  ,Product VARCHAR(160) NOT NULL
);
INSERT INTO #mytable VALUES ('Mayer','Product_1');
INSERT INTO #mytable VALUES ('Mayer','Product_1');
INSERT INTO #mytable VALUES ('Mayer','Product_2');
INSERT INTO #mytable VALUES ('Kowalsky','Product_1');
INSERT INTO #mytable VALUES ('Kowalsky','Product_2');
INSERT INTO #mytable VALUES ('Kowalsky','Product_3');
INSERT INTO #mytable VALUES ('Kowalsky','Product_4');

DECLARE @cols  NVARCHAR(MAX),
        @cols_piv NVARCHAR(MAX),
        @query NVARCHAR(MAX)
        ,@max  INT = 0;

SELECT @max = MAX(c)
FROM (
  SELECT Name, COUNT(Product) AS c
  FROM #mytable
  GROUP BY Name) AS s;

SET @cols = STUFF(     
            (SELECT ',' +  CONCAT('[',c.n, '] AS Purchase_',c.n, ' ')
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @cols_piv = STUFF(
            (SELECT ',' +  CONCAT('[',c.n, '] ')
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');        

SET @query = N'SELECT Name, ' + @cols + ' from 
            (
                select Name, Product,
                [rn] = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Product)
                from #mytable
            ) x
            pivot 
            (
                max(Product)
                for rn in (' + @cols_piv + ')
            ) p ';

-- SELECT @query; 

EXEC [dbo].[sp_executesql]
    @query;

一開始可能會很復雜，但這真的很簡單.正常的PIVOT 需要你提前知道列列表.在您的情況下，這不是一個選項，因此您需要生成列并使用 Dynamic-SQL.

It may be complicated at first but this is really simple. Normal PIVOT requires you to know column list in advance. This is not an option in your case so you need to generate column and use Dynamic-SQL.

工作原理:

1. @max 包含每行的最大列數
2. @cols 包含帶有別名的 SELECT 列列表
3. @cols_piv 包含數字列表 [1], [2], ... @max
4. 將它與普通的 PIVOT 查詢連接
5. 執行它并享受你的結果.

1. @max contains max number of columns per row
2. @cols contains SELECT column list with aliases
3. @cols_piv contains list of numbers [1], [2], ... @max
4. Concatenate it with normal PIVOT query
5. Execute it and enjoy your results.

警告:

• 我使用 sys.objects 作為我的數字生成器.您可以將其替換為您想要的(遞歸 CTE/多步 CTE/tally 表...).

• I've used sys.objects as my number generator. You can replace it with what you want (recursive CTE/multistep CTE/tally table ...).

如果您使用的是 SQL Server 2008，則需要將 CONCAT 替換為 +.

If you are using SQL Server 2008 you need to replace CONCAT with +.

這篇關于將表從行轉換為列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！