can UNPIVOT work on multiple columns?

After unpivot i want to see multiple columns, let me explain what I want with data.

CREATE TABLE #TEST
(PID INT, CLAIMID INT, Claim_ICDProc1_Code varchar(10),
Claim_ICDProc2_Code  VARCHAR(10),
Claim_ICDProc3_Code  varchar(10),
Claim_ICDProc4_Code  varchar(10),
Claim_ICDProc5_Code  varchar(10),
Claim_CPT_Code  varchar(10),
Claim_CPT2_Code varchar(10),
Claim_HCPCS_Code varchar(10)
)

insert #test
values (111, 100001, '714', '713', '712', '711', '710', '93505', '93506', '25101')
insert #test
values (222, 200002, '614', '613', '612', '611', '610', '83505', '83506', '35101')
insert #test
values (333, 300003, '514', '513', '512', '511', '510', '73505', '73506', '15101')


      select Pid,ClaimId, px, proctype, data      
            From
            (Select  Pid,ClaimId,
             1 as Px1, 1 as ProcType1, Claim_ICDProc1_Code as [1],
             2 as Px2, 1 as ProcType2, Claim_ICDProc2_Code as [2],
             3 as Px3, 1 as ProcType3, Claim_ICDProc3_Code as [3],
             4 as Px4, 1 as ProcType4, Claim_ICDProc4_Code as [4],
             5 as Px5, 1 as ProcType5, Claim_ICDProc5_Code as [5],
             6 as Px6, 2 as ProcType6, Claim_CPT_Code as [6],
             7 as Px7, 2 as ProcType7, Claim_CPT2_Code as [7],
             8 as Px8, 3 as ProcType8, Claim_HCPCS_Code as [8]
            from #test
            ) SourceTable
      UNPIVOT
            (data FOR Px IN
      ([1],[2],[3],[4],[5],[6],[7],[8])) as UnPvt


This would fail due to 'proctype' in the select list in not defined, but I do need this column in my result like below, is that possilbe?

pid                claimid         px         proctype          data
333      300003      1               1                 514
333      300003      2               1                 513
333      300003      3               1                 512
333      300003      4               1                  511
333      300003      5               1                      510
333      300003      6               2                      73505
333      300003      7               2                      73506
333      300003      8               3                      15101
222      200002      1               1                       614
222      200002      2               1                       613
222      200002      3               1                       612
222      200002      4               1                       611
222      200002      5               1                       610
222      200002      6               2                       83505
222      200002      7               2                       83506
222      200002      8               3                       35101

Thanks  a lot!!
qinyanAsked:
Who is Participating?
 
Chris LuttrellSenior Database ArchitectCommented:
Can you not get want you need with your single UNPIVOT and a case like this?
select Pid,ClaimId, px, case when px <= 5 then 1 when px in (6,7) then 2 when px = 8 then 3 end as proctype, data     
            From
            (
      Select  Pid,ClaimId,
             Claim_ICDProc1_Code as [1],
             Claim_ICDProc2_Code as [2],
             Claim_ICDProc3_Code as [3],
             Claim_ICDProc4_Code as [4],
             Claim_ICDProc5_Code as [5],
             Claim_CPT_Code as [6],
             Claim_CPT2_Code as [7],
             Claim_HCPCS_Code as [8]
            from #test 
            ) SourceTable
      UNPIVOT
            (data FOR Px IN
      ([1],[2],[3],[4],[5],[6],[7],[8])) as UnPvt

Open in new window

0
 
LowfatspreadCommented:
why not just do this?
 select Pid,ClaimId, px, proctype, data      
            From
            (Select  Pid,ClaimId,
             n as Px,case when n < 6 then 1 
                          when n in (6,7) then 2
                          else 3 end as proctype
                case n when 1 then Claim_ICDProc1_Code 
                       when 2 then Claim_ICDProc2_Code
when 3 then Claim_ICDProc3_Code 
                       when 4 then Claim_ICDProc4_Code
when 5 then Claim_ICDProc5_Code 
                       when 6 then Claim_ICDProc6_Code
when 7 then Claim_ICDProc7_Code 
                       when 8 then Claim_ICDProc8_Code
                       end as data
            from #test 
cross join (select 1 as n union select 2
union select 3 union select 4 union select 5 union select 6
union select 7 union select 8) as x
            ) SourceTable

Open in new window

0
 
qinyanAuthor Commented:
Thanks very much for the help! Both works.
0
 
qinyanAuthor Commented:
sometimes we need to think outside the box. I will never get it work if i do not change the way i do the SourceTable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.