Solved

T-SQL - How to do a simple PIVOT

Posted on 2014-09-19
12
83 Views
Last Modified: 2014-09-28
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

0
Comment
Question by:TeDeSm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332488
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
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40332511
SELECT *
FROM mytable
PIVOT(COUNT(AnalysisCode) FOR AnalysisType) AS Results

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 40332519
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:TeDeSm
ID: 40332530
Further: The table column AnalysisCode contains string values which are types of analysis, e.g. Input Error, Customer Error etc.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332539
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
 

Author Comment

by:TeDeSm
ID: 40332705
Yes, 1 row AnalysisCode, 12 columns
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332729
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
 

Author Comment

by:TeDeSm
ID: 40332789
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40334070
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
 

Accepted Solution

by:
TeDeSm earned 0 total points
ID: 40336092
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40336127
That looks way better :)
0
 

Author Closing Comment

by:TeDeSm
ID: 40348405
This solution, although not a PIVOT, did give me an output that could be used as the datasource for an Excel chart.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question