将表从行转换为列

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.