[Solved] T-SQL (Un)Pivot Table

by


I usually use dynamic sql. Something like this

create table #T
(
    ID int,
    aText1 varchar(4),
    aText2 varchar(4),
    aInt1 int,
    aInt2 int
)

insert into #T
select 1, 'ABC1', 'XYZ1', 2,  20
union
select 2, 'ABC1', 'XYZ2', 3,  25
union
select 3, 'ABC2', 'XYZ2', 1,  30
union
select 4, 'ABC2', 'XYZ1', 4,  35

declare @sql nvarchar(max)

set @sql="select aText1 "

select @sql = @sql + ', SUM(case when aText2 = ''' + aText2 + ''' then aInt1 end) as [' + aText2 + ' - aInt1] '+
                     ', SUM(case when aText2 = ''' + aText2 + ''' then aInt2 end) as [' + aText2 + ' - aInt2]'  
from 
(
    select distinct aText2 from #T
) T

set @sql = @sql + ' from #T group by aText1'

exec sp_executeSQL @sql

drop table #T

Or you can create another view (like #T2 in my next example) and use PIVOT

create table #T
(
    ID int,
    aText1 varchar(4),
    aText2 varchar(4),
    aInt1 int,
    aInt2 int
)

insert into #T
select 1, 'ABC1', 'XYZ1', 2,  20
union
select 2, 'ABC1', 'XYZ2', 3,  25
union
select 3, 'ABC2', 'XYZ2', 1,  30
union
select 4, 'ABC2', 'XYZ1', 4,  35

create table #T2
(
    aText1 varchar(4),
    aText2 varchar(20),
    aValue int
)

insert into #T2
select aText1, aText2 + ' - aInt1' as aText2, aInt1
from #T
union
select aText1, aText2 + ' - aInt2', aInt2 
from #T

declare @sql nvarchar(max), @columns nvarchar(max)
set @columns=""
select @columns = @columns + ', [' + aText2 + ']'
from (select distinct aText2 from #T2) as T

set @columns = substring(@columns, 2, len(@columns))

set @sql="
        SELECT *
        FROM
        (SELECT 
                aText1, 
                aText2, 
                aValue 
            FROM 
                #T2 
        ) AS SourceTable
        PIVOT
        (
            SUM(aValue)
            FOR aText2 in ("+@columns+')
        ) AS PivotTable'

exec sp_executeSQL @SQL

drop table #T2
drop table #T

6

solved T-SQL (Un)Pivot Table