locked
Pivot table Two columns RRS feed

  • Question

  •  

    My Table
    ColA ColB ColC
    1 23 P
    1 334 Z
    4 23 P
    4 334 K
    4 912 L
    4 18 R
    I Would like my result to be
    ColA Col23 Val23 Col334 Val334 Col912 Val912 Col18 Val18
    1 23 P 334 Z 0 0 0 0
    4 23 P 334 K 912 L 18 R
    OR
    ColA Col23 Col334 Col912 Col18
    1 P Z 0 0
    4 P K L R        

    I would appreciate your help.


    MPramod
    Friday, July 15, 2011 6:24 PM

Answers

  • DECLARE @tbl TABLE (ColA INT, ColB INT, ColC VARCHAR(5))
    
    INSERT INTO @tbl
    SELECT 1, 23, 'P' UNION ALL    
    SELECT 1, 334, 'Z'    UNION ALL 
    SELECT 4, 23, 'P'    UNION ALL 
    SELECT 4, 334, 'K'    UNION ALL 
    SELECT 4, 912, 'L'    UNION ALL 
    SELECT 4, 18, 'R' 
    
    
    SELECT ColA, [23] Col23,[334] Col334, ISNULL([912],0) Col912, ISNULL([18],0) Col18
    FROM @tbl
    PIVOT (MIN(ColC) FOR ColB IN ([23],[334],[912],[18])) pvt
    


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Marked as answer by Mpramod Monday, July 18, 2011 3:44 AM
    Friday, July 15, 2011 6:38 PM
  • dbp2784 gave you a solution if you know the values ahead of time, but here's how to do it dynamically:

    if object_id('tempdb..#t','U') is not null drop table #t
    create table #t (ColA int, ColB int, ColC char(1))
    insert #t select 1, 23,'P'
    union all select 1,334,'Z'
    union all select 4, 23,'P'
    union all select 4,334,'K'
    union all select 4,912,'L'
    union all select 4, 18,'R'
    
    declare @columnlist nvarchar(max)
        ,@outputlist nvarchar(max)
    select @columnlist=coalesce(@columnlist+',','')+quotename('col'+cast(ColB as varchar(10)))
       ,@outputlist=coalesce(@outputlist+',','')+quotename('col'+cast(ColB as varchar(10)))
                            +'=coalesce('
                            +quotename('col'+cast(ColB as varchar(10)))
                            +',''0'')'
    from (select distinct ColB from #t) t
    
    
    declare @sql nvarchar(max)
    set @sql='
    select ColA
       ,'+@outputlist+'
    from (select ColA
          ,ColumnName=''col''+cast(ColB as varchar(10))
          ,ColumnVal=ColC
       from #t) t
    pivot (min(ColumnVal) for ColumnName in ('+@columnlist+')) p'
    
    --print @sql
    exec sp_executesql @sql
    /*
    ColA col18 col23 col334 col912
    ---- ----- ----- ------ ------
      1 0   P   Z   0
      4 R   P   K   L
    */
    

     

     


    --Brad (My Blog)
    • Marked as answer by Mpramod Monday, July 18, 2011 3:44 AM
    Friday, July 15, 2011 6:44 PM
  • It's a bit tricky if you want the results be dynamic. I posted a blog post on this exact topic

    Dynamic PIVOT on multiple columns

    Your problem is very similar to first problem I describe in my blog - you just need to add Group by ColA to the idea described.

    See, if you will be able to transform the first problem to your case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Mpramod Monday, July 18, 2011 3:45 AM
    Friday, July 15, 2011 6:45 PM

All replies

  • DECLARE @tbl TABLE (ColA INT, ColB INT, ColC VARCHAR(5))
    
    INSERT INTO @tbl
    SELECT 1, 23, 'P' UNION ALL    
    SELECT 1, 334, 'Z'    UNION ALL 
    SELECT 4, 23, 'P'    UNION ALL 
    SELECT 4, 334, 'K'    UNION ALL 
    SELECT 4, 912, 'L'    UNION ALL 
    SELECT 4, 18, 'R' 
    
    
    SELECT ColA, [23] Col23,[334] Col334, ISNULL([912],0) Col912, ISNULL([18],0) Col18
    FROM @tbl
    PIVOT (MIN(ColC) FOR ColB IN ([23],[334],[912],[18])) pvt
    


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Marked as answer by Mpramod Monday, July 18, 2011 3:44 AM
    Friday, July 15, 2011 6:38 PM
  • dbp2784 gave you a solution if you know the values ahead of time, but here's how to do it dynamically:

    if object_id('tempdb..#t','U') is not null drop table #t
    create table #t (ColA int, ColB int, ColC char(1))
    insert #t select 1, 23,'P'
    union all select 1,334,'Z'
    union all select 4, 23,'P'
    union all select 4,334,'K'
    union all select 4,912,'L'
    union all select 4, 18,'R'
    
    declare @columnlist nvarchar(max)
        ,@outputlist nvarchar(max)
    select @columnlist=coalesce(@columnlist+',','')+quotename('col'+cast(ColB as varchar(10)))
       ,@outputlist=coalesce(@outputlist+',','')+quotename('col'+cast(ColB as varchar(10)))
                            +'=coalesce('
                            +quotename('col'+cast(ColB as varchar(10)))
                            +',''0'')'
    from (select distinct ColB from #t) t
    
    
    declare @sql nvarchar(max)
    set @sql='
    select ColA
       ,'+@outputlist+'
    from (select ColA
          ,ColumnName=''col''+cast(ColB as varchar(10))
          ,ColumnVal=ColC
       from #t) t
    pivot (min(ColumnVal) for ColumnName in ('+@columnlist+')) p'
    
    --print @sql
    exec sp_executesql @sql
    /*
    ColA col18 col23 col334 col912
    ---- ----- ----- ------ ------
      1 0   P   Z   0
      4 R   P   K   L
    */
    

     

     


    --Brad (My Blog)
    • Marked as answer by Mpramod Monday, July 18, 2011 3:44 AM
    Friday, July 15, 2011 6:44 PM
  • It's a bit tricky if you want the results be dynamic. I posted a blog post on this exact topic

    Dynamic PIVOT on multiple columns

    Your problem is very similar to first problem I describe in my blog - you just need to add Group by ColA to the idea described.

    See, if you will be able to transform the first problem to your case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Mpramod Monday, July 18, 2011 3:45 AM
    Friday, July 15, 2011 6:45 PM
  • Thank You dbp, brad , Naomi
    MPramod
    Monday, July 18, 2011 3:46 AM