-1

I have two tables User_details and Level_details.

User_details table:

ID     Name
1       A
2       B
3       C
4       D
5       E

Level_details table:

trns_id    Lvl   usr_id
66          1      1
66          1      5
77          1      2
77          2      3
66          2      4
66          2      3

I am expecting the result like:

trns_id   Lvl    name
66         1     A, E
66         2     D, C
77         1     B
77         2     C

Please suggest the query that should work in lower version of SQL server(below SQl 17).

Poornima
  • 39
  • 4
  • Which SQL Server version? – jarlh Jun 22 '20 at 06:19
  • @jarlh one earlier than 2017... – Nick Jun 22 '20 at 07:17
  • https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – jarlh Jun 22 '20 at 07:56
  • @DaleK , I am trying the below query: SELECT l.trns_id,l.lvl, STUFF ( ( SELECT DISTINCT ', '+ CAST(u.name AS VARCHAR(MAX)) FROM User_Details u, Level_Details l WHERE u.id=l.usr_id FOR XMl PATH('') ),1,2,'' ) as Name FROM Level_Details l GROUP BY l.trns_id, l.lvl ORDER BY l.trns_id, l.lvl – Poornima Jun 22 '20 at 10:00

1 Answers1

0

You can use STRING_AGG to get concatenated results.

SELECT l.trans_id, l.lvl, STRING_AGG(u.Name, ',') AS name
FROM Level_Details as l
INNER JOIN User_Details as u
ON u.ID = l.usr_id
group by l.trans_id, l.lvl
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58