Convert multiple rows to columns Dynamically
-
I need to convert my data set from multiple rows to columns and sum by multiple fields. Below is sample data and desired outcome. The data have 7 rows and I need to pivot this to one row for each serviced and sum the last three columns (ChargeAmount,AdjustedAmount,PaymentAmount). Here are data
SET NOCOUNT ON;
--Drop Table #T
CREATE TABLE #T (
serviceid int NOT NULL,
ProgramId int NOT NULL,
Firstbilleddate varchar(20) NULL,
CoveragePlanName char(100) NOT NULL,
ChargeAmount money NULL,
AdjustmentAmount money NULL,
PaymentAmount money NULL
)
INSERT INTO #T(serviceid, ProgramId, Firstbilleddate, CoveragePlanName,ChargeAmount,AdjustmentAmount,PaymentAmount)
SELECT '21', 82, '09/15/2015', 'Medicare',200,50,150 UNION ALL
SELECT '21', 82, 'null', 'Client',0,0,0 UNION ALL
SELECT '22', 182, '09/30/2015', 'Mediciad',500,450,50 UNION ALL
SELECT '22', 182, '01/30/2016', 'Blue Cross',1000,450,550 UNION ALL
SELECT '66', 802, 'null', 'Client',0,0,0 UNION ALL
SELECT '66', 802, '10/30/2014', 'Molina',1500,450,1150 UNION ALL
SELECT '66', 802, '01/30/2014', 'Aetna',10000,4500,5500
GO
select *
from #T
Desired Outcome: should have only three records for each unique serviced.
Thanks for your help,
Helal
-
like this?
Note, I changed the datatype of FirstBilledDate to DATE.
CREATE TABLE #T (serviceid int NOT NULL,
ProgramId int NOT NULL,
Firstbilleddate date NULL,
CoveragePlanName varchar(100) NOT NULL,
ChargeAmount money NULL,
AdjustmentAmount money NULL,
PaymentAmount money NULL
);
Populate table...
INSERT INTO #T(serviceid, ProgramId, Firstbilleddate, CoveragePlanName,ChargeAmount,AdjustmentAmount,PaymentAmount)SELECT '21', 82, '09/15/2015', 'Medicare',200,50,150 UNION ALL
SELECT '21', 82, null, 'Client',0,0,0 UNION ALL
SELECT '22', 182, '09/30/2015', 'Medicaid',500,450,50 UNION ALL
SELECT '22', 182, '01/30/2016', 'Blue Cross',1000,450,550 UNION ALL
SELECT '66', 802, null, 'Client',0,0,0 UNION ALL
SELECT '66', 802, '10/30/2014', 'Molina',1500,450,1150 UNION ALL
SELECT '66', 802, '01/30/2014', 'Aetna',10000,4500,5500;
GO
Is this what you were looking for?
SELECT serviceID, SUM(ChargeAmount) AS TotalCharge
, SUM(AdjustmentAmount) AS TotalAdjustment
, SUM(PaymentAmount) AS TotalPayment
FROM #T
GROUP BY ServiceID;
-
Not really...what I do need I believe it's called Dynamic Pivoting on multiple columns and rows. Please see attached image for the desired outcome.
Thank you,
Helal
-
hmobasher (6/24/2016)
Not really...what I do need I believe it's called Dynamic Pivoting on multiple columns and rows. Please see attached image for the desired outcome.Thank you,
Helal
no image ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Let's try this link to Google Drive folder.
The desired outcome should like this and this is only for one serviceid=66
CREATE TABLE #T1 (
serviceid int NOT NULL,
ProgramId int NOT NULL,
Firstbilleddate1 varchar(20) NULL,
Firstbilleddate2 varchar(20) NULL,
Firstbilleddate3 varchar(20) NULL,
CoveragePlanName1 char(100) NOT NULL,
CoveragePlanName2 char(100) NOT NULL,
CoveragePlanName3 char(100) NOT NULL,
ChargeAmount money NULL,
AdjustmentAmount money NULL,
PaymentAmount money NULL
)
INSERT INTO #T1(serviceid, ProgramId, Firstbilleddate1, Firstbilleddate2,Firstbilleddate3,CoveragePlanName1,CoveragePlanName2,CoveragePlanName3,ChargeAmount,AdjustmentAmount,PaymentAmount)
SELECT 66, 802, 'null', '10/30/2014', '01/30/2014','Client','Molina','Aetna', 11500,4950,6650
GO
select *
from #T1
-
Be sure to understand this and ask any questions that you might have.
DECLARE @serviceid int = 66;
DECLARE @SQL nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP(SELECT /*TOP 1*/ COUNT(*) cnt
FROM #T
WHERE serviceid = @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'SELECT *, ROW_NUMBER() OVER( PARTITION BY serviceid ORDER BY serviceid) AS row_num' + CHAR(10)
+ CHAR(9) + N'FROM #T' + 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)
+ (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 CoveragePlanName END) AS CoveragePlanName' + CAST( n AS nvarchar(3)) + CHAR(10)
FROM cteTally
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ CHAR(9) + N',SUM( ChargeAmount) AS ChargeAmount' + CHAR(10)
+ CHAR(9) + N',SUM( AdjustmentAmount) AS AdjustmentAmount' + CHAR(10)
+ CHAR(9) + N',SUM( PaymentAmount) AS PaymentAmount' + CHAR(10)
+ N'FROM CTE' + CHAR(10)
+ N'GROUP BY serviceid' + CHAR(10)
+ CHAR(9) + N',ProgramId;' + CHAR(10);
PRINT @SQL;
EXECUTE sp_executesql @SQL, N'@serviceid int', @serviceid;
References:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
-
Hi Luic,
This worked like a charm. To be honest, the script is way over my head. However, with the two articles you referenced, I will study and try to redundant the codes. I have any questions and if you don't mind, I will ask you.
Thank again,
Helal
-
Back again, I have commented and un-commented as you advised to get the full table and I get the following error. Here are the changes I made:
1: --DECLARE @serviceid int = 66;
1a: DECLARE @serviceid int ;
2: --WHERE serviceid = @serviceid--Comment this and uncomment the other part to use the full table.
3: /*GROUP BY serviceid
ORDER BY cnt DESC*/) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
3a: GROUP BY serviceid
ORDER BY cnt DESC) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
and here is the error:
Msg 1033, Level 15, State 1, Line 64
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 64
Incorrect syntax near ')'.
Helal
-
The error message tells you the problem. You didn't uncomment the TOP clause.
-
oops...I have overlooked that one. Now, it runs with no error but I get no records at all!
-
hmobasher (6/24/2016)
oops...I have overlooked that one. Now, it runs with no error but I get no records at all!Did you remove the WHERE clause from the dynamic code?
-
I figured this one out. There was another place (+ CHAR(9) + N'WHERE serviceid = @serviceid' + CHAR(10)) that I needed to comment out.
Thanks for all your help again.
Helal
-
Hi Luis,
now that I got my entire data columns in one row how do I store the result of this dynamic pivot into a temp table?
Thank You,
Helal
-
Hi Luis,
Now that with your help I got all my 50 columns on one row, I like to store the results into a temp table since this table would be a driver for most of my reporting. I tried few things like into, openrowset and none worked. Any idea how I can store the results to either a temp to permanent table?
Thank You,
Helal
-
You can use the SELECT...INTO... option using a global temp table or a perm table. A local temp table would lose the scope after finishing the dynamic code execution so it's not an option.
Be careful on this as it come with several coding problems, specially when coding against this table and having concurrent executions.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply