rows to column transpose for strings

Question

Here is the sample data:

╔═══════╦════════════════╦══════════════════╗
║ Login ║   User_type    ║ Type_login_value ║
╠═══════╬════════════════╬══════════════════╣
║ x     ║ assignee_ids   ║ a                ║
║ x     ║ subscriber_ids ║ b                ║
║ x     ║ subscriber_ids ║ c                ║
║ x     ║ subscriber_ids ║ d                ║
║ x     ║ subscriber_ids ║ e                ║
║ x     ║ subscriber_ids ║ f                ║
║ y     ║ assignee_ids   ║ g                ║
║ y     ║ subscriber_ids ║ h                ║
║ y     ║ subscriber_ids ║ i                ║
║ y     ║ subscriber_ids ║ j                ║
╚═══════╩════════════════╩══════════════════╝

output should be like

Login  assignee_ids  subscriber_ids
x      a             b,c,d,e,f
y      g             c,h,I,j

There will be some other columns also for grouping. Please suggest what should be the best way to tackle this.

Couldnt get the formatting right earlier. Sorry for that. I have edited the question now. — Crabster, Jan 02 '15 at 09:01
See http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server — Barmar, Jan 02 '15 at 09:03
@king.code I thought about using Pivot but couldnt figure how to without using aggregation. — Crabster, Jan 02 '15 at 09:30
@Barmar Thanks, I didn't know about FOR XMl. This will help. — Crabster, Jan 02 '15 at 09:31

score 1 · Answer 1 · answered Jan 02 '15 at 09:46

1

Try this,

SELECT * FROM (SELECT  DISTINCT LOGIN,
       USER_TYPE,
       Stuff((SELECT ',' + TYPE_LOGIN_VALUE
              FROM   #TEMP A
              WHERE  A.LOGIN = B.LOGIN
                 AND A.USER_TYPE = B.USER_TYPE
              FOR XML PATH('')), 1, 1, '') AS COL1
FROM   #TEMP B ) AB
PIVOT (MAX(COL1) FOR USER_TYPE IN ([assignee_ids],[subscriber_ids])) PV

answered Jan 02 '15 at 09:46

PP006

681
7
17

Hi Paul Thanks for your answer.I used following query to get the result: select b.user_login,b.user_map_type1,b.user_map_type2,b.user_login_value as 'Assignee', stuff( (select ',' + a.user_login_value from #t1 a where a.user_login=b.user_login and a.sys_prefix=b.sys_prefix and a.user_map_type1=b.user_map_type1 and a.user_map_type2=b.user_map_type2 for xml PATH('') ),1,1,'' ) as 'Subscribers' from #t2 b – Crabster Jan 03 '15 at 07:17

rows to column transpose for strings

1 Answers1