SQL - Two columns split by grouped data into multiple columns

Question

I've been tasked with coming up with a means of translating the following data:

Column_A    Column_B
----------------------
    A           AA
    A           BB
    B           CC
    B           DD
    C           EE
    C           FF
    C           GG

to

A   B   C
----------
AA  CC  EE
BB  DD  FF
        GG

and soon..

The Column_B can be NULLs or blanks, either is fine, and the Column_A would need to be dynamic. I've tried to research and have landed on PIVOT but its required 3 columns to convert. I've tried this answer but the result is

A   B   C
----------
AA  BB  CC
DD  EE  FF
GG

Column_A can have min of 25 Unique records.

score 0 · Answer 1 · answered Jul 02 '20 at 14:52

0

You can use row_number() to generate the line number and then aggregation:

select max(case when a = 'A' then b end) as a,
       max(case when a = 'B' then b end) as b,
       max(case when a = 'C' then b end) as c
from (select t.*, row_number() over (partition by a order by b) as seqnum
      from t
     ) t
group by seqnum
order by seqnum;

answered Jul 02 '20 at 14:52

Gordon Linoff

1,242,037
58
646
786

max(case when a = 'A' then b end) this will be around 25 different conditions and need to be dynamic – chiru Jul 02 '20 at 14:57
@chiru . . . If you need 25 columns, you need 25 columns and need to list them each separately. – Gordon Linoff Jul 02 '20 at 18:04
i've achieved the result bro and updated the answer. – chiru Jul 02 '20 at 18:06

chiru · Accepted Answer · 2020-07-04T04:28:50.873

As Gordon Linoff said row_number() to generate the line number and then aggregation and based on this and this answer, i just added a temp column as row_number() as pivot.

DECLARE @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX),
        @DynamicPivotQuery AS NVARCHAR(MAX); 

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(COLUMN_A)
FROM (SELECT DISTINCT COLUMN_A FROM t) AS A

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(COLUMNA) + ', '') AS '
+ QUOTENAME(COLUMN_A)
FROM (SELECT DISTINCT COLUMN_A FROM t) AS A

WITH PivotData
AS (
    SELECT COLUMN_A --The row group
        , ROW_NUMBER() OVER (
                PARTITION BY COLUMN_A ORDER BY COLUMN_A ASC
                ) AS TheRow --The value that will form columns
        ,COLUMN_B
    FROM 
        t
    )
SELECT '+@PivotSelectColumnNames+'
FROM PivotData
PIVOT(max(COLUMN_B) FOR COLUMN_A IN (
            '+@PivotColumnNames+'
            )) AS P

SQL - Two columns split by grouped data into multiple columns

2 Answers2