Transform a table from rows to columns

问题:

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

And I would like to gain the following result:

    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.

回答1:

Dynamic PIVOT is your friend :

LiveDEMO

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;

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.

How it works:

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.

Warning:

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

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