none
pivoting multiple coulmn values into multiple columns dynamically

    Question

  • Hi,

    i have found some topics related to 'pivot' in this forum. but my situation is a lit bit different.

    I have a table like the below

    create table #temp (id int, code varchar(8), routing varchar(50))

    insert into #temp(id,code ,routing )

    values(1,     'code1',      'route1')

    ,(1,'code11','route11')

    ,(2,   'code22',     'route22')

    ,(2,   'code222',    'route222')

    ,(2,   'code2333',   'route2333')

    ,(2,   'code2',      'route2')

    ,(3,   'code3',      'route3'),

    (4,    'code4',      'route4'),

    (4,    'code44',     'route44')

    ID

    CODE

    ROUTE

    1

    code1

    route1

    1

    code11

    route11

    2

    code22

    route22

    2

    code222

    route222

    2

    code2333

    route2333

    2

    code2

    route2

    3

    code3

    route3

    4

    code4

    route4

    4

    code44

    route44

    And I need to display the table below format including column names. And code/routing columns can repeat not more than 10 times.

    id

    code

    routing

    code

    routing

    code

    routing

    code

    routing

    1

    code1

    route1

    code11

    route11

    2

    code2

    route2

    code22

    route22

    code222

    route222

    code2333

    route2333

    3

    code3

    route3

    4

    code4

    route4

    code44

    route44

    I am using sql server 2012.

    i have benefited alot from this forum. a big thank you to the forum.

    I would appreciate  your help.

    Friday, March 14, 2014 10:40 PM

Answers

  • Number the rows and in a CTE and then pivot on the row number. Use MIN(CASE), not the PIVOT operator. Below I have four columns, but extending it to ten is trivial.

    WITH numbering AS (
       SELECT id, code, routing,
              row_number() OVER (PARTITION BY id ORDER BY code, routing) AS rn
       FROM   #temp
    )
    SELECT id,
           MIN(CASE rn WHEN 1 THEN code END) AS code1,
           MIN(CASE rn WHEN 1 THEN routing END) AS routing1,
           MIN(CASE rn WHEN 2 THEN code END) AS code2,
           MIN(CASE rn WHEN 2 THEN routing END) AS routing2,
           MIN(CASE rn WHEN 3 THEN code END) AS code3,
           MIN(CASE rn WHEN 3 THEN routing END) AS routing3,
           MIN(CASE rn WHEN 4 THEN code END) AS code4,
           MIN(CASE rn WHEN 4 THEN routing END) AS routing4
    FROM   numbering
    GROUP  BY id
    ORDER BY id


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SUPERZERO900 Monday, March 17, 2014 9:35 PM
    Friday, March 14, 2014 10:50 PM
  • insert into #temp(id,code ,routing )
    
    values(1,     'code1',      'route1')
    ,(1,'code11','route11')
    ,(2,   'code22',     'route22')
    ,(2,   'code222',    'route222')
    ,(2,   'code2333',   'route2333')
    ,(2,   'code2',      'route2')
    ,(3,   'code3',      'route3'),
    (4,    'code4',      'route4'),
    (4,    'code44',     'route44')
    
    ;with mycte as
    (select *,row_number() over(partition by id order by code) rn from #temp)
    select id
    , Max(Case when rn=1 Then code End) as code1
    ,Max(Case when rn=1 Then routing End) as routing1
    , Max(Case when rn=2 Then code End) as code2
    ,Max(Case when rn=2 Then routing End) as routing2
    , Max(Case when rn=3 Then code End) as code3
    ,Max(Case when rn=3 Then routing End) as routing3
    , Max(Case when rn=4 Then code End) as code4
    ,Max(Case when rn=4 Then routing End) as routing4
    , Max(Case when rn=5 Then code End) as code5
    ,Max(Case when rn=5 Then routing End) as routing5
    , Max(Case when rn=6 Then code End) as code6
    ,Max(Case when rn=6 Then routing End) as routing6
    , Max(Case when rn=7 Then code End) as code7
    ,Max(Case when rn=7 Then routing End) as routing7
    , Max(Case when rn=8 Then code End) as code8
    ,Max(Case when rn=8 Then routing End) as routing8
    , Max(Case when rn=9 Then code End) as code9
    ,Max(Case when rn=9 Then routing End) as routing9
    , Max(Case when rn=10 Then code End) as code10
    ,Max(Case when rn=10 Then routing End) as routing10
    
    from mycte
    
    group by id
    
    drop table #temp

    • Marked as answer by SUPERZERO900 Monday, March 17, 2014 9:35 PM
    Friday, March 14, 2014 10:55 PM
  • Refer below solution with PIVOT. You can extend by using Dynamic Pivot.

    --create table #temp (id int, code varchar(8), routing varchar(50))
    --insert into #temp (id,code ,routing ) values(1,'code1','route1')
    --,(1,'code11','route11'),(2,'code22','route22'),(2,'code222','route222'),(2,'code2333','route2333')
    --,(2,'code2', 'route2'),(3,'code3', 'route3'),(4,'code4','route4'),(4,'code44','route44')
    ;with cte1 as (
    	select *,row_number() over (partition by id order by code) rn
    	from #temp
    ),cte2 as (
    	select *, case
    	when id=id then 'code_' + cast(rn as varchar) end codeCol, case
    	when id=id then 'routing_' + cast(rn as varchar) end routingCol
    	from cte1
    ),cte3 as (
    	select id,code_1,routing_1,code_2,routing_2,code_3,routing_3, code_4,routing_4 from cte2
    	pivot(max(code) for codeCol in (code_1,code_2,code_3,code_4)) pvt
    	pivot(max(routing) for routingCol in (routing_1,routing_2,routing_3,routing_4)	) pvt1
    )
    select id, 
    max(code_1) code_1,max(routing_1) routing_1,
    max(code_2) code_2,max(routing_2) routing_2,
    max(code_3) code_3,max(routing_3) routing_3, 
    max(code_4) code_4,max(routing_4) routing_4
    from cte3
    group by id

    For dynamic pivot technique refer below query sample,

    DECLARE
        @cols nvarchar(max),
        @stmt nvarchar(max)
    SELECT @cols = isnull(@cols + ', ', '') + '[' + T.[NAME] + ']' FROM (SELECT distinct [NAME] FROM PIVOTTEST) as T
    SELECT @stmt = '
        SELECT *
        FROM PIVOTTEST as T
            PIVOT 
            (
                max(T.VALUE)
                for T.[NAME] in (' + @cols + ')
            ) as P'
    exec sp_executesql  @stmt = @stmt
    select * from PIVOTTEST

    Regards, RSingh


    • Edited by RSingh() Saturday, March 15, 2014 4:11 AM
    • Marked as answer by SUPERZERO900 Monday, March 17, 2014 9:35 PM
    Saturday, March 15, 2014 4:03 AM

All replies

  • Number the rows and in a CTE and then pivot on the row number. Use MIN(CASE), not the PIVOT operator. Below I have four columns, but extending it to ten is trivial.

    WITH numbering AS (
       SELECT id, code, routing,
              row_number() OVER (PARTITION BY id ORDER BY code, routing) AS rn
       FROM   #temp
    )
    SELECT id,
           MIN(CASE rn WHEN 1 THEN code END) AS code1,
           MIN(CASE rn WHEN 1 THEN routing END) AS routing1,
           MIN(CASE rn WHEN 2 THEN code END) AS code2,
           MIN(CASE rn WHEN 2 THEN routing END) AS routing2,
           MIN(CASE rn WHEN 3 THEN code END) AS code3,
           MIN(CASE rn WHEN 3 THEN routing END) AS routing3,
           MIN(CASE rn WHEN 4 THEN code END) AS code4,
           MIN(CASE rn WHEN 4 THEN routing END) AS routing4
    FROM   numbering
    GROUP  BY id
    ORDER BY id


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SUPERZERO900 Monday, March 17, 2014 9:35 PM
    Friday, March 14, 2014 10:50 PM
  • insert into #temp(id,code ,routing )
    
    values(1,     'code1',      'route1')
    ,(1,'code11','route11')
    ,(2,   'code22',     'route22')
    ,(2,   'code222',    'route222')
    ,(2,   'code2333',   'route2333')
    ,(2,   'code2',      'route2')
    ,(3,   'code3',      'route3'),
    (4,    'code4',      'route4'),
    (4,    'code44',     'route44')
    
    ;with mycte as
    (select *,row_number() over(partition by id order by code) rn from #temp)
    select id
    , Max(Case when rn=1 Then code End) as code1
    ,Max(Case when rn=1 Then routing End) as routing1
    , Max(Case when rn=2 Then code End) as code2
    ,Max(Case when rn=2 Then routing End) as routing2
    , Max(Case when rn=3 Then code End) as code3
    ,Max(Case when rn=3 Then routing End) as routing3
    , Max(Case when rn=4 Then code End) as code4
    ,Max(Case when rn=4 Then routing End) as routing4
    , Max(Case when rn=5 Then code End) as code5
    ,Max(Case when rn=5 Then routing End) as routing5
    , Max(Case when rn=6 Then code End) as code6
    ,Max(Case when rn=6 Then routing End) as routing6
    , Max(Case when rn=7 Then code End) as code7
    ,Max(Case when rn=7 Then routing End) as routing7
    , Max(Case when rn=8 Then code End) as code8
    ,Max(Case when rn=8 Then routing End) as routing8
    , Max(Case when rn=9 Then code End) as code9
    ,Max(Case when rn=9 Then routing End) as routing9
    , Max(Case when rn=10 Then code End) as code10
    ,Max(Case when rn=10 Then routing End) as routing10
    
    from mycte
    
    group by id
    
    drop table #temp

    • Marked as answer by SUPERZERO900 Monday, March 17, 2014 9:35 PM
    Friday, March 14, 2014 10:55 PM
  • Refer below solution with PIVOT. You can extend by using Dynamic Pivot.

    --create table #temp (id int, code varchar(8), routing varchar(50))
    --insert into #temp (id,code ,routing ) values(1,'code1','route1')
    --,(1,'code11','route11'),(2,'code22','route22'),(2,'code222','route222'),(2,'code2333','route2333')
    --,(2,'code2', 'route2'),(3,'code3', 'route3'),(4,'code4','route4'),(4,'code44','route44')
    ;with cte1 as (
    	select *,row_number() over (partition by id order by code) rn
    	from #temp
    ),cte2 as (
    	select *, case
    	when id=id then 'code_' + cast(rn as varchar) end codeCol, case
    	when id=id then 'routing_' + cast(rn as varchar) end routingCol
    	from cte1
    ),cte3 as (
    	select id,code_1,routing_1,code_2,routing_2,code_3,routing_3, code_4,routing_4 from cte2
    	pivot(max(code) for codeCol in (code_1,code_2,code_3,code_4)) pvt
    	pivot(max(routing) for routingCol in (routing_1,routing_2,routing_3,routing_4)	) pvt1
    )
    select id, 
    max(code_1) code_1,max(routing_1) routing_1,
    max(code_2) code_2,max(routing_2) routing_2,
    max(code_3) code_3,max(routing_3) routing_3, 
    max(code_4) code_4,max(routing_4) routing_4
    from cte3
    group by id

    For dynamic pivot technique refer below query sample,

    DECLARE
        @cols nvarchar(max),
        @stmt nvarchar(max)
    SELECT @cols = isnull(@cols + ', ', '') + '[' + T.[NAME] + ']' FROM (SELECT distinct [NAME] FROM PIVOTTEST) as T
    SELECT @stmt = '
        SELECT *
        FROM PIVOTTEST as T
            PIVOT 
            (
                max(T.VALUE)
                for T.[NAME] in (' + @cols + ')
            ) as P'
    exec sp_executesql  @stmt = @stmt
    select * from PIVOTTEST

    Regards, RSingh


    • Edited by RSingh() Saturday, March 15, 2014 4:11 AM
    • Marked as answer by SUPERZERO900 Monday, March 17, 2014 9:35 PM
    Saturday, March 15, 2014 4:03 AM
  • superZero,

    You may achieve the same using Dynamic Pivot.. Have uploaded a gallery post with respect to this scenario.. Check this:

    http://gallery.technet.microsoft.com/scriptcenter/Script-to-convert-Multiple-7d28b523


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Saturday, March 15, 2014 5:49 AM
  • For dynamic pivot technique refer below query sample,

    Since there is a known maximum, there is little reason to use dynamic SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 15, 2014 10:00 AM
  • Thank you all my friends. your help is much appreciated
    Monday, March 17, 2014 9:39 PM