SQL Server 2008 行转列

Question

SQL Server 2008 行转列

sql-serversql-server-2008pivot

3

我有两个表，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 | .....     |.......    |

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

我该如何产生这个结果？

- WorkInProgress

你为什么想在数据库中生成那个？你真的需要多达100列吗，还是一个列也可以很好地工作？（你使用的是MySQL还是SQL Server或其他什么？） - DougM

嘿，DougM，谢谢。SQL Server 2008。一个包含逗号分隔值的列将无法工作。 - WorkInProgress

3个回答

0

这里是使用多个表的解决方案。这个解决方案完全基于bluefeet的解决方案。我只是添加了用户ID。

create table #tmpUsers
(user_id int, user_name varchar(255));
insert into #tmpUsers values (1,'Jack');
insert into #tmpUsers values (2,'Peggy');
insert into #tmpUsers values (3,'Bruce');
insert into #tmpUsers values (4,'Chuck');


create table #tmpItems
(user_id int, category varchar(255));
insert into #tmpItems values(1,'Shoes');
insert into #tmpItems values(1,'Tie');
insert into #tmpItems values(1,'Glass');

insert into #tmpItems values(2,'Shoe');
insert into #tmpItems values(2,'Skirt');
insert into #tmpItems values(2,'Bat');
insert into #tmpItems values(2,'Cat');

insert into #tmpItems values(3,'Laptop');
insert into #tmpItems values(3,'Beer');

insert into #tmpItems values(4,'Cell Phone');


select TU.user_name,TI.category from #tmpUsers TU inner join #tmpItems TI on TU.user_id=TI.user_id


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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Category'+cast(row_number() over(partition by TU.[user_id] 
                                                                      order by TU.[user_id]) as varchar(3))) 
                    from #tmpUsers TU inner join #tmpItems TI on TU.user_id=TI.user_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT [user_name],' + @cols + ' 
              from
              (
                select TU.[user_name], TI.category,
                  ''Category''+cast(row_number() over(partition by TU.[user_id] 
                                                    order by TU.[user_id] ) as varchar(3)) rn
                from #tmpUsers TU inner join #tmpItems TI on TU.user_id=TI.user_id
              ) d
              pivot 
              (
                  max(category)
                  for rn in (' + @cols + ')
              ) p '

execute(@query)


drop table #tmpUsers
drop table #tmpItems

- WorkInProgress

0

Sql Server 允许您对数据进行透视。然而，像其他关系型数据库一样，即使使用 PIVOT，仍需要在查询开始时知道结果将有多少列（以及是什么类型）。在这里，您最好的选择是使用查询，结合动态 SQL（在运行时在代码中构建查询字符串），首先找出谁拥有最多类别，然后构建一个查询来透视您的数据以查找那么多项目。

处理未知列数的透视的常规解决方案是在调用服务器的代码中从客户端进行透视。

- Joel Coehoorn

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

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

由于您正在使用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。

针对您所述情况，需要使用动态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。两者都会给出结果：

|  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 |