SQL - Pivot help

Transact-SQL
1

Hi guys,

Could someone help me on this query below.

DECLARE @MyTable TABLE(
Period varchar(30)
, [MonthName] varchar(30)
, [Code] varchar(30)
, [Category] varchar(30)
, [breached] float
, [total] float

);

INSERT INTO @MyTable

SELECT '2015-16','May','04Q','Cardiology',10,100 UNION ALL

SELECT '2015-16','May','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','Jul','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','Jul','04Q','Cardiology',15,70 UNION ALL

SELECT '2015-16','Aug','04Q','Urology',10,100 UNION ALL

SELECT '2015-16','Aug','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Urology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Urology',20,50 UNION ALL
SELECT '2015-16','April','04Q','ENT',20,50 UNION ALL

SELECT '2015-16','April','04Q','ENT',15,70 UNION ALL

SELECT '2015-16','May','04Q','ENT',10,100 UNION ALL

SELECT '2015-16','May','04Q','ENT',20,50 UNION ALL

SELECT '2015-16','June','04Q','ENT',20,50 UNION ALL

SELECT '2015-16','June','04Q','ENT',20,50 UNION ALL

SELECT '2015-16','April','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','April','04Q','Cardiology',15,70 UNION ALL

SELECT '2015-16','May','04Q','Cardiology',10,100 UNION ALL

SELECT '2015-16','May','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','Jul','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','Jul','04Q','Cardiology',15,70 UNION ALL

SELECT '2015-16','Aug','04Q','Urology',10,100 UNION ALL

SELECT '2015-16','Aug','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Urology',20,50 UNION ALL

SELECT '2015-16','June','04Q','Urology',20,50 UNION ALL
SELECT '2015-16','April','04Q','ENT',20,50 UNION ALL

SELECT '2015-16','April','04Q','ENT',15,70 UNION ALL

SELECT '2015-16','Dec','04Q','xxxxxx',10,100 UNION ALL

SELECT '2015-16','Dec','04Q','xxxxxx',20,50 UNION ALL

SELECT '2015-16','Dec','04Q','xxxxxx',20,50 UNION ALL

SELECT '2015-16','Jan','04Q','xxxxxx',20,50 UNION ALL

SELECT '2015-16','Jan','04Q','Cardiology',20,50 UNION ALL

SELECT '2015-16','Jan','04Q','Cardiology',15,70

;WITH CTE_Spec AS (
SELECT [Category]
,SUM([breached]) AS Total_Breached
,SUM([Total]) AS YTD_Total
, ROUND((CAST(SUM(breached) AS FLOAT)/CAST(SUM(Total) AS FLOAT))*100, 2) AS PCT_Breached
,Period
,[MonthName]

FROM  @MyTable
GROUP BY    [Category]	
     ,Period
    ,[MonthName]
)

SELECT [Category]
,CONVERT(varchar,AVG(cast([Total_Breached] as int)),1) AS AVG_Activity
,MAX(CASE WHEN [MonthName] = 'April' THEN ISNULL([PCT_Breached],0) END) AS '1'
,MAX(CASE WHEN [MonthName] = 'May' THEN ISNULL([PCT_Breached],0) END) AS '2'
,MAX(CASE WHEN [MonthName] = 'June' THEN ISNULL([PCT_Breached],0) END) AS '3'
,MAX(CASE WHEN [MonthName] = 'July' THEN ISNULL([PCT_Breached],0) END) AS '4'
,MAX(CASE WHEN [MonthName] = 'August' THEN ISNULL([PCT_Breached],0) END) AS '5'
,MAX(CASE WHEN [MonthName] = 'Dec' THEN ISNULL([PCT_Breached],0) END) AS '9'
,MAX(CASE WHEN [MonthName] = 'Jan' THEN ISNULL([PCT_Breached],0) END) AS '12'

----- and so on
,AVG(ISNULL([PCT_Breached],0) ) AS YTD
FROM CTE_Spec
GROUP BY [Category]
--GROUP BY ROLLUP( [Category]);

Ideally what I wanted is a copy of the attached sample report.
Thanks in advance
Ziggy

rp1.png728×376 16.5 KB

2

Not sure if this is what you want. I just changed the last part of the query to

SELECT isnull([Category], 'All Specialities') as Category
,CONVERT(varchar,AVG(cast([Total_Breached] as int)),1) AS AVG_Activity
,MAX(CASE WHEN [MonthName] = 'April' THEN ISNULL([PCT_Breached],0) END)	AS '1'
,MAX(CASE WHEN [MonthName] = 'May' THEN ISNULL([PCT_Breached],0) END)	AS '2'
,MAX(CASE WHEN [MonthName] = 'June' THEN ISNULL([PCT_Breached],0) END)	AS '3'
,MAX(CASE WHEN [MonthName] = 'July' THEN ISNULL([PCT_Breached],0) END)	AS '4'
,MAX(CASE WHEN [MonthName] = 'August' THEN ISNULL([PCT_Breached],0) END)	AS '5'
,MAX(CASE WHEN [MonthName] = 'Dec' THEN ISNULL([PCT_Breached],0) END)	AS '9'
,MAX(CASE WHEN [MonthName] = 'Jan' THEN ISNULL([PCT_Breached],0) END)	AS '12'

----- and so on
,AVG(ISNULL([PCT_Breached],0) ) AS YTD
FROM CTE_Spec
--GROUP BY [Category]
GROUP BY ROLLUP( [Category]);

Try that with your data. Also, in your query you just have the months numbered. but that's easy to change

1 Like