Solved

T-SQL - How to do a simple PIVOT

Posted on 2014-09-19
12
81 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 48

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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

839 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