Link to home
Start Free TrialLog in
Avatar of qinyan
qinyan

asked on

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!!
ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of qinyan
qinyan

ASKER

Thanks very much for the help! Both works.
Avatar of qinyan

ASKER

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.