TeDeSm
asked on
T-SQL - How to do a simple PIVOT
I need to count a number of Analysis codes within a period and pivot the results.
I need the result to show row name AnalysisCode with the column names being the Analysis type e.g. Anal1, Anal2 etc. The row is to contain the count of each analysis type under its respective heading.
I need the result to show row name AnalysisCode with the column names being the Analysis type e.g. Anal1, Anal2 etc. The row is to contain the count of each analysis type under its respective heading.
ALTER PROCEDURE dbo.reportsp_PreAlertAnalysisAllByPeriodSummed
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS SELECT AnalysisCode
FROM PreAlerts
WHERE (LedgerType <> N'') AND (DateEntered > CONVERT(DATETIME, '2014-06-01 00:00:00', 102)) AND (AnalysisCode <> N'')
/* SET NOCOUNT ON */
RETURN
Where is the field Analysis Type? Or is it one that should be automatically created? If the latter, how many do you want (you can't have a variable number without some very nifty coding)?
SELECT *
FROM mytable
PIVOT(COUNT(AnalysisCode) FOR AnalysisType) AS Results
ASKER
I figured I might need the IN clause. I will need 12 columns and perhaps a couple of extras on the end to handle the future requirement to add new analysis codes. The extras could have a value of zero.
IN (Anal1, Anal2, Anal3 ...........)
The resulting Pivot will be used in a project to create a Pie Chart in Excel.
IN (Anal1, Anal2, Anal3 ...........)
The resulting Pivot will be used in a project to create a Pie Chart in Excel.
ASKER
Further: The table column AnalysisCode contains string values which are types of analysis, e.g. Input Error, Customer Error etc.
What output do you want, and what is your relevant table? At the moment, it would seem you want an output of 1 row and 12 columns.
ASKER
Yes, 1 row AnalysisCode, 12 columns
That's not actually a Pivot. A Pivot has numerical values as the output. I believe you want nvarchar as the output.
Something like this:
select (select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 0 row FETCH NEXT 1 ROW ONLY) as Anal1,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 1 row FETCH NEXT 1 ROW ONLY) as Anal2,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 2 row FETCH NEXT 1 ROW ONLY) as Anal3,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 3 row FETCH NEXT 1 ROW ONLY) as Anal4,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 4 row FETCH NEXT 1 ROW ONLY) as Anal5,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 5 row FETCH NEXT 1 ROW ONLY) as Anal6,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 6 row FETCH NEXT 1 ROW ONLY) as Anal7,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 7 row FETCH NEXT 1 ROW ONLY) as Anal8,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 8 row FETCH NEXT 1 ROW ONLY) as Anal9,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 9 row FETCH NEXT 1 ROW ONLY) as Anal10,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 10 row FETCH NEXT 1 ROW ONLY) as Anal11,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 11 row FETCH NEXT 1 ROW ONLY) as Anal12
Something like this:
select (select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 0 row FETCH NEXT 1 ROW ONLY) as Anal1,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 1 row FETCH NEXT 1 ROW ONLY) as Anal2,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 2 row FETCH NEXT 1 ROW ONLY) as Anal3,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 3 row FETCH NEXT 1 ROW ONLY) as Anal4,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 4 row FETCH NEXT 1 ROW ONLY) as Anal5,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 5 row FETCH NEXT 1 ROW ONLY) as Anal6,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 6 row FETCH NEXT 1 ROW ONLY) as Anal7,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 7 row FETCH NEXT 1 ROW ONLY) as Anal8,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 8 row FETCH NEXT 1 ROW ONLY) as Anal9,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 9 row FETCH NEXT 1 ROW ONLY) as Anal10,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 10 row FETCH NEXT 1 ROW ONLY) as Anal11,
(select AnalysisCode FROM PreAlerts Order by AnalysisCode OFFSET 11 row FETCH NEXT 1 ROW ONLY) as Anal12
ASKER
hmm. Looks interesting. It is numerical values I need. The count of each value in the column AnalysisCode.
Anal1 Anal2 Anal3 Anal4 ......
CountOfAnalysisCode 20 15 7 10
Anal1 Anal2 Anal3 Anal4 ......
CountOfAnalysisCode 20 15 7 10
If you know the number of columns, then the easiest is "ye olde fashioned" case expressions with a group by IMO
for just one row of data:
You can also do COUNT() instead of SUM()
OR, if dealing with non-numeric data use MAX() instead of SUM()
or you can have all 3 if you want :)
plus more, AVG() MIN() etc
for just one row of data:
SELECT
SUM(CASE WHEN code = 'anal01' THEN value END) AS anal01
, SUM(CASE WHEN code = 'anal02' THEN value END) AS anal02
, SUM(CASE WHEN code = 'anal03' THEN value END) AS anal03
, SUM(CASE WHEN code = 'anal04' THEN value END) AS anal04
, SUM(CASE WHEN code = 'anal05' THEN value END) AS anal05
, SUM(CASE WHEN code = 'anal06' THEN value END) AS anal06
, SUM(CASE WHEN code = 'anal07' THEN value END) AS anal07
, SUM(CASE WHEN code = 'anal08' THEN value END) AS anal08
, SUM(CASE WHEN code = 'anal09' THEN value END) AS anal09
, SUM(CASE WHEN code = 'anal10' THEN value END) AS anal10
, SUM(CASE WHEN code = 'anal11' THEN value END) AS anal11
, SUM(CASE WHEN code = 'anal12' THEN value END) AS anal12
FROM the_data
;
and if you need rows of some kind:SELECT
some_row_val
, SUM(CASE WHEN code = 'anal01' THEN value END) AS anal01
, SUM(CASE WHEN code = 'anal02' THEN value END) AS anal02
, SUM(CASE WHEN code = 'anal03' THEN value END) AS anal03
, SUM(CASE WHEN code = 'anal04' THEN value END) AS anal04
, SUM(CASE WHEN code = 'anal05' THEN value END) AS anal05
, SUM(CASE WHEN code = 'anal06' THEN value END) AS anal06
, SUM(CASE WHEN code = 'anal07' THEN value END) AS anal07
, SUM(CASE WHEN code = 'anal08' THEN value END) AS anal08
, SUM(CASE WHEN code = 'anal09' THEN value END) AS anal09
, SUM(CASE WHEN code = 'anal10' THEN value END) AS anal10
, SUM(CASE WHEN code = 'anal11' THEN value END) AS anal11
, SUM(CASE WHEN code = 'anal12' THEN value END) AS anal12
FROM the_data
GROUP BY
some_row_val
;
You can also do COUNT() instead of SUM()
OR, if dealing with non-numeric data use MAX() instead of SUM()
or you can have all 3 if you want :)
plus more, AVG() MIN() etc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That looks way better :)
ASKER
This solution, although not a PIVOT, did give me an output that could be used as the datasource for an Excel chart.