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:
and here are same data in one row:
Any help will be appreciated,
and the output:
answered Jul 29, 2015 at 08:37 AM
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 "
// 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 + ',' , '') +
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
answered Jul 30, 2015 at 08:18 PM
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?
answered Aug 14, 2015 at 11:19 PM