April 11, 2016 at 6:50 am
Could you show me how to pivot this so I get the YearMonth as column headers and the rows being PatientStatus and Location?
create table #TEST
(
YearMonth varchar(10),
PatientStatus varchar(10),
Location varchar(10),
PatientVisits int
)
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2015-10','IN','B4','103')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2015-11','IN','B4','92')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2015-12','IN','B4','97')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-01','IN','B4','89')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-02','IN','B4','80')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-03','IN','B4','100')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-04','IN','B4','11')
April 11, 2016 at 6:58 am
See below. This has hardcoded columns, if you want to make it dynamic have a look at the linked article.
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
SELECT *
FROM
(SELECT PatientStatus, Location, PatientVisits, YearMonth
FROM #TEST) AS SourceTable
PIVOT
(
SUM(PatientVisits)
FOR YearMonth IN ([2015-10],[2015-11],[2015-12],[2016-01],[2016-02],[2016-03],[2016-04])
) AS PivotTable;
April 11, 2016 at 7:06 am
Thanks and yes, I need it to be dynamic. Thanks for the article
April 11, 2016 at 7:11 am
This can also be achieved using crosstabs which have the benefit of being simpler and faster. You'll still need to come up with a dynamic solution though.
SELECT
PatientStatus,
Location,
MAX(CASE WHEN YearMonth = '2015-10' THEN PatientVisits END) AS '2015-10',
MAX(CASE WHEN YearMonth = '2015-11' THEN PatientVisits END) AS '2015-11',
MAX(CASE WHEN YearMonth = '2015-12' THEN PatientVisits END) AS '2015-12',
MAX(CASE WHEN YearMonth = '2016-01' THEN PatientVisits END) AS '2016-01',
MAX(CASE WHEN YearMonth = '2016-02' THEN PatientVisits END) AS '2016-02',
MAX(CASE WHEN YearMonth = '2016-03' THEN PatientVisits END) AS '2016-03',
MAX(CASE WHEN YearMonth = '2016-04' THEN PatientVisits END) AS '2016-04'
FROM #TEST
GROUP BY
PatientStatus,
Location
Cheers,
April 11, 2016 at 8:09 am
Hey this is an example using a dynamic solution.
create table #TEST
(
YearMonth varchar(10),
PatientStatus varchar(10),
Location varchar(10),
PatientVisits int
)
DECLARE @sql VARCHAR(MAX)
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2015-10','IN','B4','103')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2015-11','IN','B4','92')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2015-12','IN','B4','97')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-01','IN','B4','89')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-02','IN','B4','80')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-03','IN','B4','100')
insert into #TEST(YearMonth, PatientStatus, Location, PatientVisits) values('2016-04','IN','B4','11');
WITH myValues (n) AS
(
SELECT DISTINCT YearMonth FROM #TEST
)
SELECT @sql =
'SELECT PatientStatus, Location' +
(SELECT ' , MAX(CASE WHEN YearMonth = ' + '''' + CAST( n AS VARCHAR(7)) + '''' + ' THEN PatientVisits END) AS ' + '''' + CAST( n AS VARCHAR(7)) + ''''
FROM myValues FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)') +
' FROM #TEST' +
' GROUP BY PatientStatus, Location'
EXECUTE (@sql)
DROP TABLE #TEST
Pretty cool technique I learnt from Luis. 😉
April 11, 2016 at 8:42 am
yb751 (4/11/2016)
Pretty cool technique I learnt from Luis. 😉
You just made my day 🙂
I learned this concatenation method from Wayne Sheffield, which he explains it in here: http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
And combined it with the great article on dynamic cross tabs from Jeff Moden: http://qa.sqlservercentral.com/articles/Crosstab/65048/
April 11, 2016 at 8:51 am
Thanx.
April 11, 2016 at 9:21 am
Luis Cazares (4/11/2016)
yb751 (4/11/2016)
Pretty cool technique I learnt from Luis. 😉You just made my day 🙂
I learned this concatenation method from Wayne Sheffield, which he explains it in here: http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
And combined it with the great article on dynamic cross tabs from Jeff Moden: http://qa.sqlservercentral.com/articles/Crosstab/65048/
That's awesome! Great example on how knowledge is contagious.
April 11, 2016 at 9:27 am
OK. Now for some more knowledge transfer..................this stored procedure containing the dynamic sql is used for an SSRS report. The dataset isn't pulling the fields because of issues with SSRS. Any thoughts?
April 11, 2016 at 9:36 am
NineIron (4/11/2016)
OK. Now for some more knowledge transfer..................this stored procedure containing the dynamic sql is used for an SSRS report. The dataset isn't pulling the fields because of issues with SSRS. Any thoughts?
You should probably need to use the pivoting capabilities of SSRS instead of using a dynamic approach.
SSRS requires a static dataset to be able to map the columns correctly. If you're changing them on every call, you'll keep getting errors.
If you're pivoting on T-SQL, you need a static amount of columns with consistent names. You might also need columns for the "column name" instead of using "Period 1", "Period 2", ..., "Period N".
I always forget how to pivot on SSRS and have to figure it out again (I almost never use SSRS). But I remember is not really difficult with the appropriate object.
April 11, 2016 at 9:39 am
Thanx. I didn't know you could pivot with SSRS but, am willing to learn.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply