将表从行转换为列 [英] Transform a table from rows to columns

问题描述

我得到了以下形式的有序 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 +.

这篇关于将表从行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！