CODEWITHSUNDEEP
sqlsql-serverpivot
user3525244
user3525244

Reputation: 11

SQL - Pivot 2 columns

I have the following sql

CREATE TABLE #t ( id varchar(10)
                 ,Rscd varchar(10)
                 ,Accd varchar(10))

INSERT INTO #t Values ('EHC','A','B') 
INSERT INTO #t Values ('DEN','C','D')

select EHC,DEN
from
(
  select id as id2, value
  from #t
cross apply
  (
    select id,Rscd union  select id,Accd
    ) c (id2,value)
) d
pivot
(
  max(value)
  for id2 in ([EHC],[DEN])
) piv;

This produce output as 

EHC DEN
B    D

But I need the Out put as

EHC DEN EHC2 DEN2
B    D    A   C

Is this possible ? Thanks for you assistance.

Upvotes: 1

Views: 67

Answers (1)

Taryn
Taryn

Reputation: 247650

You need to implement a windowing function like row_number() to get the result. This will create a sequence number based on the id. It appears that you have a specific order that you want the data in, if so then I would create a column when you are unpivoting via CROSS APPLY that will be used to order the data:

select EHC1,DEN1, EHC2, DEN2
from
(
  select value,
    id2 = id
          + cast(row_number() over(partition by id
                                    order by so desc) as varchar(10))
  from #t
  cross apply
  (
    select id, Rscd, 1 union  
    select id, Accd, 2
  ) c (id2,value, so)
) d
pivot
(
  max(value)
  for id2 in ([EHC1],[DEN1], [EHC2],[DEN2])
) piv;

See SQL Fiddle with Demo

Upvotes: 1

Related Questions