Convert multiple rows to columns Dynamically

Question

Post reply

Convert multiple rows to columns Dynamically

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

hmobasher Right there with Babe Points: 760 More actions · Answer 1

Thank you. More specifically, where do I insert those statements. I tried this with no avail (my inserts are in Bold)

if object_id('tempdb.dbo.##Servicepayment') is not null drop table ##Servicepayment

DECLARE @serviceid int ;

DECLARE @sql nvarchar(max);

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

cteTally(n) AS(

SELECT TOP(SELECT TOP 1 COUNT(*) cnt

FROM #Servicepayment

--WHERE serviceid in( @serviceid)--Comment this and uncomment the other part to use the full table.

GROUP BY serviceid

ORDER BY cnt DESC) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E a, E b

)

SELECT @sql =

N'WITH CTE AS(' + CHAR(10)

+ CHAR(9) + N'INTO ##Servicepayment' + CHAR(9) +

N'SELECT *

, ROW_NUMBER() OVER( PARTITION BY serviceid ORDER BY serviceid) AS row_num' + CHAR(10)

+ CHAR(9) + N'FROM #Servicepayment' + CHAR(10)

--+ CHAR(9) + N'WHERE serviceid = @serviceid' + CHAR(10)

+ N')' + CHAR(10)

+ CHAR(9) + N'SELECT serviceid ' + CHAR(10)

+ CHAR(9) + N',ProgramId ' + CHAR(10)

+ CHAR(9) + N',ProgramName ' + CHAR(10)

+ CHAR(9) + N',ClinicianId ' + CHAR(10)

+ CHAR(9) + N',ClinicianName ' + CHAR(10)

+ CHAR(9) + N',ClientId ' + CHAR(10)

+ CHAR(9) + N',ClientName ' + CHAR(10)

+ CHAR(9) + N',Servicedate ' + CHAR(10)

+ CHAR(9) + N',ServiceYear ' + CHAR(10)

+ CHAR(9) + N',ServiceMonth ' + CHAR(10)

+ CHAR(9) + N',ServiceDay ' + CHAR(10)

+ CHAR(9) + N',ServiceBegTime ' + CHAR(10)

+ CHAR(9) + N',ServiceEndTime ' + CHAR(10)

+ CHAR(9) + N',Serviceminutes ' + CHAR(10)

+ CHAR(9) + N',ServiceHours ' + CHAR(10)

+ CHAR(9) + N',ServiceStatus ' + CHAR(10)

+ CHAR(9) + N',CPTCode ' + CHAR(10)

+ CHAR(9) + N',ProcedureCodeName ' + CHAR(10)

+ CHAR(9) + N',UnitType ' + CHAR(10)

+ CHAR(9) + N',ServiceUnits ' + CHAR(10)

--+ CHAR(9) + N',Diagnosis ' + CHAR(10)

+ CHAR(9) + N',LastBillableCoveragePlan ' + CHAR(10)

+ CHAR(9) + N',LastBillablePayerId ' + CHAR(10)

+ CHAR(9) + N',LastBillablePayer ' + CHAR(10)

+ CHAR(9) + N',LastBillableCoveragePlanCOBOrder' + CHAR(10)

+ CHAR(9) + N',DaysInProgram ' + CHAR(10)

+ CHAR(9) + N',Donotbill ' + CHAR(10)

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN Firstbilleddate END) AS Firstbilleddate' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN CoveragePlanid END) AS CoveragePlanid' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN CoveragePlanName END) AS CoveragePlanName' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN ChargeAmount END) AS ChargeAmount' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN AdjustedAmount END) AS AdjustedAmount' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN PaymentAmount END) AS PaymentAmount' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN Billflag END) AS BillFlag' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN NumberofUnits END) AS NumberofUnits' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN Payerid END) AS Payerid' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN PayerName END) AS PayerName' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ (SELECT CHAR(9) + ',MAX( CASE WHEN row_num = ' + CAST( n AS nvarchar(3)) + ' THEN Chargepriority END) AS Chargepriority' + CAST( n AS nvarchar(3)) + CHAR(10) FROM cteTally FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')

+ CHAR(10) + N'FROM CTE' + CHAR(10)

+ N'group by serviceid' + CHAR(10)

+ CHAR(9) + N',ProgramId' + CHAR(10)

+ CHAR(9) + N',ProgramName' + CHAR(10)

+ CHAR(9) + N',ClinicianId' + CHAR(10)

+ CHAR(9) + N',ClinicianName' + CHAR(10)

+ CHAR(9) + N',ClientId' + CHAR(10)

+ CHAR(9) + N',ClientName' + CHAR(10)