Solved

T-SQL - How to do a simple PIVOT

Posted on 2014-09-19
12
78 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now