How to combine two columns from rows?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

foreign key should do the job, you can learn more about it here

https://www.w3schools.com/sql/sql_foreignkey.asp

and then you can use inner join: https://www.w3schools.com/sql/sql_join_inner.asp