0

I have table with following structure:

enter image description here

I want to show it like this:

enter image description here

Maximum 5 users will be there per LocIC and AuditID

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1497569
  • 83
  • 2
  • 12
  • 4
    This has been asked and answered thousands of times on SO and probably close to a million times around the rest of the internet. Look for either PIVOT or crosstab. – Sean Lange May 08 '18 at 15:09
  • 1
    Here's one. https://stackoverflow.com/questions/50218241/sql-how-do-i-transpose-and-group-data-into-static-columns/50218653#50218653 – Sean Lange May 08 '18 at 15:10
  • @SeanLange Pivot questions should be restricted on SO. – dfundako May 08 '18 at 15:38
  • @dfundako I agree. It is crazy how many times a day the same question like this one comes in. – Sean Lange May 08 '18 at 15:39
  • Have you tried Google???? There are tons of example on this!!! – Eric May 08 '18 at 16:08
  • Who would upvote this silly question??? This shows no effort and should be downvoted instead. – Eric May 08 '18 at 16:09

2 Answers2

2

You want conditional aggregation:

select LocID, AuditID,
       max(case when Seq = 1 then userID end) User1,
       max(case when Seq = 2 then userID end) User2,
       max(case when Seq = 3 then userID end) User3,
       max(case when Seq = 4 then userID end) User4,   
       max(case when Seq = 5 then userID end) User5   
from (select *,
              row_number() over (partition by LocID, AuditID order by userID) Seq
      from table a 
     ) t
group by LocID, AuditID;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
-3

You can use group_concat feature SQL server.

select LocID,AuditID, group_concat(userID) listUser from table group by LocID, AuditID

Here later you can use listUser column values to show in table

user1479816
  • 1
  • 2
  • There is no group_concat in sql server. There is a STRING_AGG function added but not until sql server 2017 and many people are not upgraded that much yet. – Sean Lange May 08 '18 at 15:16
  • 1
    Besides, `GROUP_CONCAT` or `STRING_AGG` has nothing to do with converting rows to columns. That's pivoting – Panagiotis Kanavos May 08 '18 at 15:20