SQL rows into columns with forming groups

user_15018997 0
Viewed 92

Please help me with this task, I actually spent all day long, trying to solve this problem.

I have this table

PaintedObjectID CanOfPaintID ColorOfCan
3 3 R
3 6 G
3 9 B
6 10 R
6 17 R
6 18 G
6 19 B

and need to get this one:

PaintedObjectID numberOfGroup RedCanOfPaintID GreenCanOfPaintID BlueCanOfPaintID
3 1 3 6 9
6 1 10 18 19
6 2 17 NULL NULL

so for each PaintedObjectID we need subsequently form first group of RedCan & GreenCan & BlueCan and assign it numberOfGroup =1, if there are more cans spent for that PaintedObjectID, then fill second group (assign numberOfGroup=2), etc.

Demo (without solution, but with filled source table)

Do you have any idea of how this can be done?

sql-server
1 Answers

First use ROW_NUMBER to generate the numberOfGroup then pivot. eg

with q as
(
  select  *, row_number() over ( partition by PaintedObjectId, ColorOfCan order by CanOfPaintId) numberOfGroup
  from [PaintedObject_CanOfPaint]
)
select PaintedObjectID, 
       numberOfGroup, 
       max(case when ColorOfCan = 'R' then CanOfPaintId else null end) RedCanOfPaintId,
       max(case when ColorOfCan = 'G' then CanOfPaintId else null end) GreenCanOfPaintId,
       max(case when ColorOfCan = 'B' then CanOfPaintId else null end) BlueCanOfPaintId
from q
group by PaintedObjectID, numberOfGroup
order by PaintedObjectID, numberOfGroup

outputs

 PaintedObjectID numberOfGroup        RedCanOfPaintId GreenCanOfPaintId BlueCanOfPaintId
--------------- -------------------- --------------- ----------------- ----------------
3               1                    3               6                 9
5               1                    11              13                15
5               2                    12              14                16
6               1                    10              18                19
6               2                    17              NULL              NULL
user_72977000
Related