CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Lawrence Choi
Lawrence Choi

Reputation: 17

Pivot table with multiple aggregate value

I am trying to pivot below table, is that possible?

But if I aggregate them by Code, Value A or B will be gone because of it.

From

Name Code Value
Kevin Code1 A
Kevin Code1 B
Kevin Code2 C
Kevin Code3 D
Tom Code1 E
Tom Code2 F
Tom Code3 G

To

Name Code1 Code2 Code3
Kevin A C D
Kevin B C D
Tom E F G

Query I tried....

DECLARE @cols AS NVARCHAR(max), @query AS NVARCHAR(max);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Code) 
FROM Table c
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(max)'),1,1,'')

set @query = 'with mapping as 
             (SELECT 
                Name
                , ' + @cols + ' 
             from (select name
                       , Code
                       , Value
                    from Table) x
             pivot 
             (max(Code)
             for Name in (' + @cols + ')) p )' 

execute(@query)

Tried myself but I can only achieve below

Name Code1 Code2 Code3
Kevin A or B(depend on aggregated max or min) C D
Tom E F G

Thanks in advance

Upvotes: 0

Views: 198

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use row_number() and some arithmetic:

select name,
       max(case when code = 'Code1' then value end) as code1,
       max(case when code = 'Code2' then value end) as code2,
       max(case when code = 'Code3' then value end) as code3
from (select t.*,
             row_number() over (partition by name, code order by (select null)) as seqnum
      from t
     ) t
group by name, seqnum;

It is not clear if the dynamic SQL is due to a dynamic number of columns or an attempt to solve this problem. But this can be adapted for dynamic SQL as well.

Upvotes: 0

Serg
Serg

Reputation: 22811

This is a fixed column list version. You should build your dynamic one using this template.

select Name 
  , first_value(Code1) over(partition by Name order by rn) Code1
  , first_value(Code2) over(partition by Name order by rn) Code2
  , first_value(Code3) over(partition by Name order by rn) Code3
from (
   select t.* , row_number() over(partition by Name, Code order by Value) rn
   from tbl t
   ) t
pivot (max(value) for code in (Code1, Code2, Code3)) p
order by Name;

Upvotes: 1

Related Questions