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.

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

Open in new window

TeDeSmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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)?
0
GanapathiFacets DeveloperCommented:
SELECT *
FROM mytable
PIVOT(COUNT(AnalysisCode) FOR AnalysisType) AS Results

Open in new window

0
TeDeSmAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TeDeSmAuthor Commented:
Further: The table column AnalysisCode contains string values which are types of analysis, e.g. Input Error, Customer Error etc.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
TeDeSmAuthor Commented:
Yes, 1 row AnalysisCode, 12 columns
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
0
TeDeSmAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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:
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
;

Open in new window

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
;

Open in new window


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
0
TeDeSmAuthor Commented:
Taking a fresh look at the problem I came up with the following which is usuable to plot an Excel chart.
SELECT        AnalysisCode, COUNT(1) AS AnalysisCount
   FROM            PreAlerts
   WHERE        (LedgerType <> N'') AND (DateEntered > CONVERT(DATETIME, '2014-06-01 00:00:00', 102))
   GROUP BY AnalysisCode
   HAVING        (AnalysisCode <> N'')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
That looks way better :)
0
TeDeSmAuthor Commented:
This solution, although not a PIVOT, did give me an output that could be used as the datasource for an Excel chart.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.