SQL 将行转换为列 [英] SQL Convert Rows To Columns
本文介绍了SQL 将行转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我知道这已经被问过几次了,但我找不到任何适合我的例子的解决方案.
I know this has been asked a few times before, but I can't find any solution that fits my example.
我目前拥有使用某些页面的用户权限表.表格如下所示:
I currently have a table of user permissions to use certain pages. The table would look like this:
UserID pagename pageid
-----------------------------------
1 home 1
1 contacts 3
3 home 1
2 links 2
如何从这些数据中生成一个表格,其中所有页面名称都以列的形式列出,表格的每一行都用于一个用户 ID,列值根据原始表格是否有显示为 0 或 1该特定页面的条目,例如:
How would I generate a table from this data where I have all the page names tabulated as columns and each row of the table is for a User ID, the column values display 0 or 1 based on whether or not the original table had an entry for that particular page, for instance:
UserID home links contacts
-------------------------------------
1 1 0 1
2 0 1 0
3 1 0 0
非常感谢您的帮助!
推荐答案
在@t 中构建你的表:
Build your table in @t:
declare @t as table (UserID int, pagename nvarchar(20), pageid int);
insert into @t values (1,'home',1),(1,'contacts',3),(3,'home',1),(2,'links',2);
旋转它:
select UserID,
case when home is null then 0 else 1 end as home,
case when links is null then 0 else 1 end as links,
case when contacts is null then 0 else 1 end as contacts
from @t
pivot (
max(pageid) for pagename in ([home],[links],[contacts])
) pivotT
UserID home links contacts
----------- ----------- ----------- -----------
1 1 0 1
2 0 1 0
3 1 0 0
这篇关于SQL 将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文