How to combine two columns from rows?

Question

How to combine two columns from one row of a table?

Talbe

ID	RulesID	ObjectID	TypeID
1	9	56	1
7	9	43	3
24	9	73	2
31	9	94	3
33	9	20	4
45	9	1008	8
46	9	1009	7

Result

RulesID	ObjectID	TypeID	ObjectID2	TypeID2
9	56	1	43	3
9	73	2	94	3
9	20	4	1008	8
9	1009	7	null	null

What is the rule behind the desired result? Why not 43 and 73 in the same row? — Martin Smith, Oct 28 '21 at 18:17
You can Use Pivot, this is was posted years ago https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — greektreat, Oct 28 '21 at 18:27

score 3 · Accepted Answer · answered Oct 28 '21 at 18:25

Using row_number() and the modulus to determine column. Then it becomes a small matter for a conditional aggregegation.

Example

Select RulesID
      ,ObjectID  = max( case when Col=1 then ObjectID end)
      ,TypeID    = max( case when Col=1 then TypeID end)
      ,ObjectID2 = max( case when Col=0 then ObjectID end)
      ,TypeID2   = max( case when Col=0 then TypeID end)
 From (
        Select * 
              ,Grp = (1+row_number() over (partition by RulesID order by ID)) / 2
              ,Col = row_number() over (partition by RulesID order by ID) % 2
         From YourTable
      ) A
 Group By RulesID,Grp

Results

How to combine two columns from rows?

1 Answers1