How to combine multiple SQL rows into columns dynamically

Question

I have a table with

+-------+-------+-----------------+
| P1_ID | P2_ID | Relationship_ID |
+-------+-------+-----------------+
|     1 |    21 |               3 |
|     1 |    32 |               3 |
|     2 |    45 |               2 |
|     2 |    65 |               1 |
|     3 |    98 |               3 |
|     3 |    94 |               4 |
+-------+-------+-----------------+

I want the final table to look like:

+-------+--------+--------+------+------+
| P1_ID | P2_ID1 | P2_ID2 | RID1 | RID2 |
+-------+--------+--------+------+------+
|     1 |     21 |     32 |    3 |    3 |
|     2 |     45 |     65 |    2 |    1 |
|     3 |     98 |     94 |    3 |    4 |
+-------+--------+--------+------+------+

I am not sure which direction to go with this. I am trying to use a pivot but I can not seem to make it work. Maybe I am doing it wrong.

Add the rows (3,99,5) and (4,55,5) to the sample data, and adjust the result accordingly. (To make the problem clearer.) — jarlh
– jarlh, Commented Jan 13, 2020 at 14:22
What are you using to order your data? Realationship_IS? And when you say "final table" I hope you just mean a query because storing data like that is not a good idea. — Sean Lange
– Sean Lange, Commented Jan 13, 2020 at 14:28
Yes it is a query for a report. I meant Relationship_ID. I will correct that sorry. — kziaj930
– kziaj930, Commented Jan 13, 2020 at 14:36
Why does this need to be dynamic? If you have only two rows per id then dynamic SQL is not needed. — Gordon Linoff
– Gordon Linoff, Commented Jan 13, 2020 at 14:54
Some Ids have more than 2 rows per ID. this was just an example — kziaj930
– kziaj930, Commented Jan 13, 2020 at 15:18

Sean Lange · Accepted Answer · 2020-01-13 15:52:57Z

You can use conditional aggregation for this. This isn't exactly what you stated for output because the ordering of your data is a little funky. But this should point you in the right direction.

declare @Something table
(
    P1_ID int
    , P2_ID int
    , Realationship_ID int
)

insert @Something values
(1,21,3)
, (1,32,3)
, (2,45,2)
, (2,65,1)
, (3,98,3)
, (3,94,4)

select P1_ID
    , P2_ID1 = MAX(Case when RowNum = 1 then P2_ID end)
    , P2_ID2 = max(case when RowNum = 2 then P2_ID end)
    , RID1 = MAX(Case when RowNum = 1 then Realationship_ID end)
    , RID2 = max(case when RowNum = 2 then Realationship_ID end)
from
(
    select *
        , RowNum = ROW_NUMBER() over(partition by s.P1_ID order by Realationship_ID)
    from @Something s
) x
group by x.P1_ID

--EDIT--

Here is a fully dynamic solution for this. I switched to using a temp table because a table variable would be out of scope for dynamic sql. Obviously in your situation you would be using a persistent table. This will order the output by P1_ID and the columns within each row by Realationship_ID.

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something
(
    P1_ID int
    , P2_ID int
    , Realationship_ID int
)

insert #Something values
(1,21,3)
, (1,32,3)
, (2,45,2)
, (2,65,1)
, (3,98,3)
, (3,94,4)
;

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by P1_ID order by P1_ID';
declare @StaticPortion nvarchar(2000) = 
'with OrderedResults as
(
    select *
        , RowNum = ROW_NUMBER() over(partition by s.P1_ID order by Realationship_ID)
    from #Something s
)
select P1_ID';

with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a cross join E1 b), --10E+2 or 100 rows
cteTally(N) AS 
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)

select @DynamicPortion = @DynamicPortion + 
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then P2_ID end) as P2_ID' + CAST(N as varchar(6)) + CHAR(10) +
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then Realationship_ID end) as RID' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= 
(
    select top 1 Count(*)
    from #Something
    group by P1_ID
    order by COUNT(*) desc
)

declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;

exec sp_executesql @SqlToExecute

Hi, there is no number order in the data.. for example person ID is the main employee. Person2 ID is the emergency contacts of the main employee. So it can be any random numbers.
OK then this will work unless you have more than 2 people. Then you will need to make a dynamic version of this.
what would be a dynamic version of this because for some instances there would be 2 or more people.
Do you have a fixed max number or does it need to be fully dynamic?
fully dynamic. The numbers in the fields are ID's and do not need to be ordered.

Collectives™ on Stack Overflow

How to combine multiple SQL rows into columns dynamically

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related