Multi PIVOT Select

I have the following Data Structure in my Db which stores lots of different (dynamic) values based off a formsite import, I need to write a query which will return all possible combination of values so that I can use it in further data processing.

Table structure:

What I would like as an output is as follows:

I have tried all types of Pivoting, RowNubers, Ranks but non seem to be giving me the exact output i need.

declare @table table (ID int, Code nvarchar(10), value nvarchar(30))


insert into @table(Id,Code,Value)
values
(1,N'A',N'z'),
(1,N'B',N'y'),
(1,N'A',N'x'),
(1,N'C',N'w'),
(1,N'B',N'v'),
(2,N'A',N'x'),
(2,N'B',N'y')

select * from @table

select 
    *
into #r1
from (
    select 
        row_number() over(partition by id order by code) as RowNumberColumn ,
        id,
        code,
        value 
    from @table

) as t
pivot (
    max(value) for code in (A,B,C)
) as p
select * from #r1

select 
    * 
from #r1 as p
join #r1 as c on p.ID = c.ID and p.RowNumberColumn <> c.ID


select 
    * 
from #r1 as p
join #r1 as c on p.ID = c.ID and p.RowNumberColumn <> c.ID

select distinct 
    p.Id,
    p.RowNumberColumn,
    coalesce(c.a,p.a) as A,
    coalesce(c.b,p.b) as B,
    coalesce(c.c,p.c) as C
from #r1 as p, #r1 as c 
where p.ID = c.ID
    and (p.a <> c.a
    or p.b <> c.b
    or p.c <> c.c
    )


drop table #r1t

There will be multiple Codes which can have multiple values associated to an ID. So hard coding a number of joins is not an option

Answers

You can achieve the desired output by first assigning row numbers to each row within each ID group, and then using conditional aggregation to pivot the data. Here's a query that accomplishes this:

WITH NumberedRows AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS RowNum
    FROM
        @table
),
PivotedData AS (
    SELECT
        ID,
        MAX(CASE WHEN Code = 'A' THEN Value END) AS A,
        MAX(CASE WHEN Code = 'B' THEN Value END) AS B,
        MAX(CASE WHEN Code = 'C' THEN Value END) AS C
    FROM
        NumberedRows
    GROUP BY
        ID, RowNum
)
SELECT
    ID,
    A,
    B,
    C
FROM
    PivotedData
ORDER BY
    ID, RowNum;

In this query:

• NumberedRows CTE assigns a row number to each row within each ID group. This ensures that rows with the same ID are assigned consecutive row numbers starting from 1.
• PivotedData CTE pivots the data using conditional aggregation to transform rows into columns based on the Code column values.
• Finally, the main query selects the ID and pivoted columns (A, B, C) from the PivotedData CTE, giving you the desired output.