sql-server - SQL Server 2008 行到列

Question

我有两个表，tempUsers 和 tempItems。这两个表具有一对多的关系。

当我对这两个表使用内部联接时，结果如下所示:

**user | Category**
Jack | Shoes
Jack | Tie
Jack | Glass
Peggy | Shoe
Peggy | Skirt
Peggy | Bat
Peggy | Cat
Bruce | Laptop
Bruce | Beer
Chuck | Cell Phone

我更喜欢这样的结果:

**User | Category1  | Category2 | Category3 | Category4**
Jack   | Shoes      | Tie       | Glass     | .....
Peggy  | Shoe       | Skirt     | Bat       | Cat
Bruce  | Laptop     | Beer      |.....      |......
Chuck  | Cell Phone | .....     |.......    |

类别中不同类别的数量是动态的 - 给定项目可以有任意数量的类别。

我怎样才能产生这个结果？

Answer 1

有几种方法可以将数据从行转换为列。

既然你使用的是SQL Server 2008，那你就可以使用PIVOT函数了。

我建议使用 row_number() 函数来协助旋转数据。如果您有已知数量的值，则可以对查询进行硬编码:

select user, category1, category2, category3, category4
from
(
  select [user], category,
    'Category'+cast(row_number() over(partition by [user] 
                                      order by [user]) as varchar(3)) rn
  from yt
) d
pivot
(
  max(category)
  for rn in (category1, category2, category3, category4)
) piv;

参见 SQL Fiddle with Demo .

对于您的情况，您声明您将有未知数量的值需要作为列。在这种情况下，您将希望使用动态 SQL 生成要执行的查询字符串:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Category'+cast(row_number() over(partition by [user] 
                                                                      order by [user]) as varchar(3))) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [user],' + @cols + ' 
              from
              (
                select [user], category,
                  ''Category''+cast(row_number() over(partition by [user] 
                                                    order by [user]) as varchar(3)) rn
                from yt
              ) d
              pivot 
              (
                  max(category)
                  for rn in (' + @cols + ')
              ) p '

execute(@query)

参见 SQL Fiddle with Demo .两者都给出一个结果:

|  USER |  CATEGORY1 | CATEGORY2 | CATEGORY3 | CATEGORY4 |
----------------------------------------------------------
| Bruce |     Laptop |      Beer |    (null) |    (null) |
| Chuck | Cell Phone |    (null) |    (null) |    (null) |
|  Jack |      Shoes |       Tie |     Glass |    (null) |
| Peggy |       Shoe |     Skirt |       Bat |       Cat |