Transposing columns to tables
This is probably very easy but I can not get my head around it. SQL Server 2008. I have the following:
SELECT userId, accesslevel
FROM accesscontrol.dbo.BADGELINK
the results I get are
user id accesslevel
72 开发者_开发技巧 1
72 5
72 9
89 1
89 4
91 9
91 11
what I would like is
user id acesslevels
72 1,5,9
89 1,4
91 9,11
Any ideas anybody?
In SQL Server can you do something like:
Select B.user_id
, Stuff(
(
Select ',' + B1.AccessLevel
From AccessControl.dbo.BadgeLink As B1
Where B1.user_id = B.user_id
Group By B1.AccessLevel
For Xml Path('')
), 1, 2, '') As AccessLevels
From AccessControl.dbo.BadgeLink As B
Group By B.user_id
There is an absolutely brilliant article on this topic here: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ It lives in my bookmarks and I refer to it often. If this does not fully answer your question, please let me know
精彩评论