开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜