Solved

T-SQL - How to do a simple PIVOT

Posted on 2014-09-19
12
82 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Title # Comments Views Activity
push and Pull replication 31 47
Freeze portion of datamart 2 22
SQL Syntax 6 41
Why do I get an error when executing a stored procedure using SQL Server 2008? 1 6
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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