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!!
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],[
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER