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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

qinyanAuthor Commented:
Thanks very much for the help! Both works.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.