question

helal avatar image
helal asked

Transpose Columns to Rows in SQL 2012

I have my data (attached) in cloumns and I like to transpose them into rows. I tried Unipovt but since most of the fileds are dynamics and not fixed, I am having issues to set Unipivot dynamically. Here is current data in colums: create table #columnstorows ( ThirdPartyPayer varchar (5), PatientIDNumber varchar (5), ClaimLineNumber varchar (2), PaidAmount float ) go insert into #columnstorows (ThirdPartyPayer, PatientIDNumber, ClaimLineNumber ,PaidAmount) select 'U001','00001','01','76.58' UNION ALL select 'U002','00002','02','19.53' Go and here are same data in one row: ThirdParty_1 ThirdParty_2 PatientIDNumber_1 PatientIDNumber_2 PaidAmount_1 Paid Amount_2 U001 U002 000001 000002 76.58 19.53 Any help will be appreciated, Thank You
sql-server-2012pivot
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Just to be clear, what is the desired output?
0 Likes 0 ·
helal avatar image helal commented ·
Data depicted in one Row
0 Likes 0 ·
sabinweb avatar image
sabinweb answered
DECLARE @vcPatientID_List AS VARCHAR(500) ,@vcPatientID_Antet AS VARCHAR(500) ,@vcThirdParty_Antet AS VARCHAR(500) ,@vcPaidAmount_Antet AS VARCHAR(500) ,@nvcSQL AS NVARCHAR(MAX) ='' SELECT @vcPatientID_List = ISNULL(@vcPatientID_List + ',' , '') + '[' + CAST(PatientIDNumber AS VARCHAR(50)) + ']' FROM #columnstorows GRoUP BY PatientIDNumber SELECT @vcPatientID_Antet = ISNULL(@vcPatientID_Antet+',','') + '[' + CAST(PatientIDNumber AS VARCHAR(50)) + '] AS ' + 'PatientIDNumber_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT PatientIDNumber , ROW_NUMBER() OVER(ORDER BY PatientIDNumber ASC) AS RN FROM #columnstorows GROUP BY PatientIDNumber)A SELECT @vcThirdParty_Antet = ISNULL(@vcThirdParty_Antet + ',' , '') + '[' + CAST(PatientIDNumber AS VARCHAR(50)) + '] AS ' + 'ThirdParty_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT PatientIDNumber , ROW_NUMBER() OVER(ORDER BY PatientIDNumber ASC) AS RN FROM #columnstorows GROUP BY PatientIDNumber)A SELECT @vcPaidAmount_Antet = ISNULL(@vcPaidAmount_Antet + ',' , '') + '[' + CAST(PatientIDNumber AS VARCHAR(50)) + '] AS ' + 'PaidAmount_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT PatientIDNumber , ROW_NUMBER() OVER(ORDER BY PatientIDNumber ASC) AS RN FROM #columnstorows GROUP BY PatientIDNumber)A PRINT @vcPatientID_Antet PRINT @vcPatientID_List PRINT @vcThirdParty_Antet PRINT @vcPaidAmount_Antet SET @nvcSQL = ' SELECT B.* ,C.* ,A.* FROM ( SELECT ' + @vcPaidAmount_Antet + ' FROM (SELECT PatientIDNumber,PaidAmount FROM #columnstorows) AS CSR PIVOT ( SUM(PaidAmount) FOR PatientIDNumber IN ('+ @vcPatientID_List + ') ) PV )A CROSS JOIN ( SELECT ' + @vcThirdParty_Antet + ' FROM (SELECT PatientIDNumber,ThirdPartyPayer FROM #columnstorows) AS CSR PIVOT( MAX(ThirdPartyPayer) FOR PatientIDNumber IN ('+ @vcPatientID_List + ') ) PV )B CROSS JOIN ( SELECT '+@vcPatientID_Antet + ' FROM (SELECT PatientIDNumber FROM #columnstorows) AS CSR PIVOT ( MAX(PatientIDNumber) FOR PatientIDNumber IN ('+ @vcPatientID_List + ') ) PV )C' EXEC sp_ExecuteSQL @nvcSQL and the output: ThirdParty_1 ThirdParty_2 PatientIDNumber_1 PatientIDNumber_2 PaidAmount_1 PaidAmount_2 U001 U002 00001 00002 76.58 19.53
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

helal avatar image helal commented ·
This is awesome. Two questions: 1- Are there two solutions one with Cross join and one without? or all part of one solution? 2 - How about the following scenario where only Thirdparty varies and Paidamount be summed up. Like: ThirdParty_1 ThirdParty_2 PatientIDNumber PaidAmount U001 U002 00001 96.11 Is this the case for using Pivot with dynamic variable for Thirdparty? Thank You
0 Likes 0 ·
sabinweb avatar image sabinweb commented ·
1-This is all part of one solution. I used `CROSS JOIN` to unify the results of the `PIVOT` 2 - Try it and see what you obtain (I don't have time right now)
0 Likes 0 ·
helal avatar image helal commented ·
I did and worked like a charm...Thank you. Anyway, I can put this #1 in a Temp Table?
0 Likes 0 ·
helal avatar image helal commented ·
Just a quick questions: how do I add more static (not dynamic ) fields to the script? do I need to do the two parts the select and the cross join?
0 Likes 0 ·
sabinweb avatar image sabinweb commented ·
To add more static columns, try to add them in `SELECT PatientIDNumber,PaidAmount FROM #columnstorows) AS CSR` and in the upper `SELECT`
0 Likes 0 ·
helal avatar image
helal answered
Thank you. I am running to a very strange situation. Sp works just fine with sample data, and real data, as long as I limit records to only a few. However, when apply to six month worth of data (10K+ records), I get a strange "Syntax Error" message. Here is the Script that works for limited records but not for few thousands. Error Message: "Category Timestamp Duration Message Line Position Error 7/30/2015 2:16:08 PM 0:00:14.533 - SQL Server Database Error: Incorrect syntax near the keyword 'AS'. 624 0 " Any Idea? Sp Script: /**********************************************************************/ if object_id('tempdb..##PLPT') is not null begin drop table ##PLPT end /**********************************************************************/ DECLARE @ThirParty_List AS VARCHAR(128) ,@PatientID_Antet AS VARCHAR(128) ,@ThirdParty_Antet AS VARCHAR(128) ,@PaidAmount_Antet AS VARCHAR(128) ,@nvcSQL AS NVARCHAR(MAX) ='' SELECT @ThirParty_List = ISNULL(@ThirParty_List + ',' , '') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + ']' FROM ##PLTAB Group BY ThirdPartyPayer SELECT @PatientID_Antet = ISNULL(@PatientID_Antet+',','') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + '] AS ' + 'PatientIDNumber_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT ThirdPartyPayer , ROW_NUMBER() OVER(ORDER BY ThirdPartyPayer ASC) AS RN FROM ##PLTAB GROUP BY ThirdPartyPayer)A SELECT @ThirdParty_Antet = ISNULL(@ThirdParty_Antet + ',' , '') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + '] AS ' + 'ThirdParty_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT ThirdPartyPayer , ROW_NUMBER() OVER(ORDER BY ThirdPartyPayer ASC) AS RN FROM ##PLTAB GROUP BY ThirdPartyPayer)A SELECT @PaidAmount_Antet = ISNULL(@PaidAmount_Antet + ',' , '') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + '] AS ' + 'PaidAmount_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT ThirdPartyPayer , ROW_NUMBER() OVER(ORDER BY ThirdPartyPayer ASC) AS RN FROM ##PLTAB GROUP BY ThirdPartyPayer)A SET @nvcSQL = ' SELECT B.* ,C.* ,A.* INTO ##PLPT FROM ( SELECT ' + @PaidAmount_Antet + ' FROM (SELECT ThirdPartyPayer,PaidAmount FROM ##PLTAB) AS CSR PIVOT ( SUM(PaidAmount) FOR ThirdPartyPayer IN ('+ @ThirParty_List + ') ) PV )A CROSS JOIN ( SELECT ' + @ThirdParty_Antet + ' FROM (SELECT ThirdPartyPayer FROM ##PLTAB) AS CSR PIVOT( MAX(ThirdPartyPayer) FOR ThirdPartyPayer IN ('+ @ThirParty_List + ') ) PV )B CROSS JOIN ( SELECT '+@PatientID_Antet + ' FROM (SELECT PatientIDNumber,ThirdPartyPayer FROM ##PLTAB) AS CSR PIVOT ( MAX(PatientIDNumber) FOR ThirdPartyPayer IN ('+ @ThirParty_List + ') ) PV )C' EXEC sp_ExecuteSQL @nvcSQL select distinct * From ##PLPT
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sabinweb avatar image sabinweb commented ·
Add some clause to real data. Also keep in mind that you are using `VARCHAR(128)` - this could reach his upper limit, so try to increase it - like VARCHAR(4000)
0 Likes 0 ·
helal avatar image
helal answered
HI there, I have now a variation of the above scenario. Same structure except that PaidAmount is now split into six different columns (Paid1 to Paid6). Thus data loos like this: 3rdParty_1 3rdParty_2 PIDNo_1 PIDNo_2 Paid1 Paid2 Paid3 Paid4 Paid5 Paid6 U001 U002 00001 00002 50 20 10 5 0 NULL How do I modify the codes to accommodates for the six paid amounts? Thank You,
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.