I have table with following structure:
I want to show it like this:
Maximum 5 users will be there per LocIC and AuditID
-
4This 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– Sean Lange2018-05-08 15:09:52 +00:00Commented May 8, 2018 at 15:09
-
1Here's one. stackoverflow.com/questions/50218241/…Sean Lange– Sean Lange2018-05-08 15:10:43 +00:00Commented May 8, 2018 at 15:10
-
@SeanLange Pivot questions should be restricted on SO.dfundako– dfundako2018-05-08 15:38:05 +00:00Commented May 8, 2018 at 15:38
-
@dfundako I agree. It is crazy how many times a day the same question like this one comes in.Sean Lange– Sean Lange2018-05-08 15:39:26 +00:00Commented May 8, 2018 at 15:39
-
Have you tried Google???? There are tons of example on this!!!Eric– Eric2018-05-08 16:08:20 +00:00Commented May 8, 2018 at 16:08
|
Show 1 more comment
2 Answers
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;
1 Comment
Harshada
Thank you Yogesh for your valuable help
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
2 Comments
Sean Lange
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.
Panagiotis Kanavos
Besides,
GROUP_CONCAT or STRING_AGG has nothing to do with converting rows to columns. That's pivoting