August 15, 2015 at 7:52 am
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,
Paid2float,
Paid3float
)
go
insert into #columnstorows
(Payer, ID,ClM,Paid1, Paid2,Paid3)
select 'U001','00001','01','76.58','153.48','53.48' UNION ALL
select 'U002','00001','01','19.53','96.43','200'
Go
select *
From #columnstorows
Desired result:
Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200
Any help will be greatly appreciated.
Thank You
August 16, 2015 at 7:41 pm
What if there is a 3rd and 4th payer? How does the data look like? And how did the ID2 become 002 when nothing of such was in data? Can you provide more insight for this...
August 17, 2015 at 2:14 am
With Pivot you effectively turn columns into rows and rows into columns http://sqlrambling.net/2013/10/31/pivot-basic-example/ - your example appear to want to turn an entire table into one row.
'Payer1' uses the 'Payer' value in the first row, 'Payer2' is the 'Payer' value of the second row. If there were 100 rows it looks like it would go to 'Payer100'. This means that for 100 rows of data there would be 600 columns, taking into account the other columns from the original table.
Could you explain in a little more detail what it is you are trying to achieve? We may then be able to clarify this for you.
August 17, 2015 at 3:37 am
Looks like the whole table need to be converted into single row rather then transposed. Is it what you need really?
August 17, 2015 at 11:50 am
What I am trying to achieve is this. I am going from Claim Billed (CH) to Claim Paid (PL).
Patient ID - CHClaim No - CHThird Party Code - CHBilled - CHPatient ID - PLThird Party Code- PLClaim No- PL Paid Amount - PL
1000 2800 CARE 275 1000 CARE 2800 76.58
1000 2800 CARE 275 1000 U007 2800 19.53
here I have two records for the same patient, same claims, same billed insurance but payment comes from two or more different insurances. The true Billed amount is $275 and not $275*2. So, I need to get the following for this patient with claim ID (2800). Not repeating common fields, the rest are:
........Billed - CH Third Part Code 1 - PL Third Part Code 2 - PL Paid Amount 1 - PL Paid Amount 1 - PL
275 CARE U007 76.58 19.53
Hope this is clear now as what I am trying to achieve. I get into same situation with joining to payment adjustment table.
Thank You
August 18, 2015 at 4:05 am
PLEASE find the attached code here we are creating Columns on the basis of Third_Party_Code_PL column value (ie [CARE],[U007] ) and storing Paid_Amount_PL value in it
create table #temp
(
Patient_IDvarchar(500),
CH_Claim_Novarchar(500),
Third_Party_Code_CHvarchar(500),
Billed_CHvarchar(500),
Patient_ID_PLvarchar(500),
Third_Party_Code_PLvarchar(500),
Claim_No_PLvarchar(500),
Paid_Amount_PLvarchar(500)
)
insert into #temp
(
Patient_ID,CH_Claim_No,Third_Party_Code_CH,Billed_CH
,Patient_ID_PL,Third_Party_Code_PL,Claim_No_PL
,Paid_Amount_PL)
values
('1000' ,'2800' ,'CARE' ,'275', '1000' ,'CARE', '2800', '76.58')
,('1000' ,'2800' ,'CARE' ,'275', '1000' ,'U007', '2800', '19.53')
sELECT
Patient_ID,CH_Claim_No,Third_Party_Code_CH,Billed_CH
,Patient_ID_PL,Claim_No_PL,[CARE],[U007]
FROM #TEMP AS A
PIVOT
(
MAX(Paid_Amount_PL) FOR Third_Party_Code_PL IN ( [CARE],[U007])
) AS P
August 18, 2015 at 4:50 am
helal.mobasher 13209 (8/17/2015)
payment comes from two or more different insurances.
So you need dynamic pyvoting. See http://qa.sqlservercentral.com/scripts/pivot/110353/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply