none
Pivot Table TSQL

    Question

  • My company has a database in SQL Server 2008.  I have read only access to that database.  I need to create Pivot Table queries in TSQL (using Visual Studio 2008), will eventually be putting this data in SSRS for reporting.  Below is the data and below that is what I need the outcome to look like.  I have tried several options, but the date seems to be the issue.  Any help would be great.

    ticket_number

    impact_date

    severity

    327696

    11/25/12 8:00 AM

    3

    327325

    11/16/12 11:12 PM

    3

    326511

    11/4/12 8:01 AM

    3

    326352

    11/1/12 5:00 PM

    3

    326253

    10/31/12 4:03 PM

    3

    324647

    10/9/12 11:08 AM

    3

    324336

    10/4/12 4:30 AM

    3

    324103

    10/1/12 3:28 PM

    3

    323942

    9/28/12 6:07 AM

    1

    323724

    9/25/12 5:00 PM

    3

    322093

    9/4/12 12:50 PM

    3

    322306

    9/1/12 8:00 AM

    3

    321019

    8/18/12 12:04 PM

    3

    320030

    8/6/12 10:37 AM

    4

    319901

    8/3/12 8:17 AM

    3

    319274

    7/23/12 1:33 PM

    3

    318878

    7/21/12 12:04 AM

    3

    318979

    7/20/12 10:37 PM

    1

    318748

    7/19/12 3:04 PM

    1

    318593

    7/18/12 4:42 AM

    3

    I need the above data to look like this:

    severity

    Jul-12

    Aug-12

    Sep-12

    Oct-12

    Nov-12

    1

    2

    1

    2

    1

    3

    3

    2

    3

    3

    4

    4

    1


    Thank you, Martha

    Monday, November 26, 2012 8:27 PM

Answers

  • SELECT Severity,[January 2012],[February 2012],[March 2012],[April 2012],[May 2012],[June 2012],[July 2012],[August 2012],[September 2012],[October 2012],[November 2012],[December 2012]
    FROM   (SELECT severity,ticket_number,DATENAME(month, impact_date) + ' '
                                          + CAST(YEAR(impact_date) AS CHAR(4)) AS impact_date
            FROM   assets) ps
           PIVOT ( COUNT (ticket_number)
                 FOR impact_date IN ( [January 2012],[February 2012],[March 2012],[April 2012],[May 2012],[June 2012],[July 2012],[August 2012],[September 2012],[October 2012],[November 2012],[December 2012]) ) AS pvt 
    

    • Marked as answer by mp80237 Tuesday, November 27, 2012 3:20 PM
    Monday, November 26, 2012 10:00 PM
    Moderator
  • Hi mp80237

    It seems others have answered already but I decided to paste mine anyways.

    declare @temp table (ticket_number int,impact_date datetime,severity int)
    insert into @temp (ticket_number,impact_date,severity) values
    (327696,'11/25/12 8:00 AM',3),
    (327325,'11/16/12 11:12 PM',3),
    (326511,'11/4/12 8:01 AM',3),
    (326352,'11/1/12 5:00 PM',3),
    (326253,'10/31/12 4:03 PM',3),
    (324647,'10/9/12 11:08 AM',3),
    (324336,'10/4/12 4:30 AM',3),
    (324103,'10/1/12 3:28 PM',3),
    (323942,'9/28/12 6:07 AM',1),
    (323724,'9/25/12 5:00 PM',3),
    (322093,'9/4/12 12:50 PM',3),
    (322306,'9/1/12 8:00 AM',3),
    (321019,'8/18/12 12:04 PM',3),
    (320030,'8/6/12 10:37 AM',4),
    (319901,'8/3/12 8:17 AM',3),
    (319274,'7/23/12 1:33 PM',3),
    (318878,'7/21/12 12:04 AM',3),
    (318979,'7/20/12 10:37 PM',1),
    (318748,'7/19/12 3:04 PM',1),
    (318593,'7/18/12 4:42 AM',3)
    
    select severity, [Jul-12] ,[Aug-12],[Sep-12],[Oct-12],[Nov-12]
    from 
    (
    	select 
    	 [severity]
    	,[ticket_number]
    	,[label] = CONVERT(varchar(100),LEFT(DATENAME(MM,impact_date),3)) + '-' + CONVERT(varchar(2),RIGHT(DATEPART(YY,impact_date),2))
    	from @temp) p
    	pivot (count([ticket_number]) for [label] IN ([Jul-12], [Aug-12], [Sep-12], [Oct-12], [Nov-12])
    ) AS pt
    order by pt.[severity]


    Pérez

    • Marked as answer by mp80237 Tuesday, November 27, 2012 3:20 PM
    Monday, November 26, 2012 10:21 PM

All replies

  • If you are using SSRS then you can do the pivoting there instead doing it in the db.

    Adding a Matrix (Reporting Services)
    http://msdn.microsoft.com/en-us/library/ms157334(v=SQL.100).aspx

    If you still want to do this in the query then search in this forum for "dynamic pivot".

    http://social.msdn.microsoft.com/forums/en-us/transactsql/thread/3B447DD2-8072-4932-87EF-E9474B843757

    For more assitance, please post table schema, sample data in the form of "insert" statements, and expected result.

     


    AMB

    Some guidelines for posting questions...

    Monday, November 26, 2012 9:16 PM
    Moderator
  • Is it possible to use a query like this?  I was under the impression with the new SQL doing a pivot table was a ton easier than doing a CREATE TABLE.  Also the link and I have tried, I am having issues with the dates (headers by month).  That is part reason why I tried to use the PIVOT

    SELECT Severity, [January 2012], [February 2012], [March 2012], [April 2012], [May 2012], [June 2012], [July 2012], [August 2012], [September 2012], [October 2012], [November 2012], [December 2012]
    FROM
    (SELECT severity, ticket_number FROM assets ) ps
    PIVOT
    (
    COUNT (ticket_number)
    FOR impact_date IN
    ( [January 2012], [February 2012], [March 2012], [April 2012], [May 2012], [June 2012], [July 2012], [August 2012], [September 2012], [October 2012], [November 2012], [December 2012])
    ) AS pvt


    Thank you, Martha

    Monday, November 26, 2012 9:35 PM
  • Hi mp80237...

    Answering your question, yes you can use that query, the disadvantage is you need to get the name of the fields like Jul 12, Aug 12, Sep 12... etc etc for a given period, you need to review the Dynamic Pivot topic and SQL Dynamic Queries.

    Regards.


    Sergio Sánchez Arias

    Monday, November 26, 2012 9:58 PM
  • SELECT Severity,[January 2012],[February 2012],[March 2012],[April 2012],[May 2012],[June 2012],[July 2012],[August 2012],[September 2012],[October 2012],[November 2012],[December 2012]
    FROM   (SELECT severity,ticket_number,DATENAME(month, impact_date) + ' '
                                          + CAST(YEAR(impact_date) AS CHAR(4)) AS impact_date
            FROM   assets) ps
           PIVOT ( COUNT (ticket_number)
                 FOR impact_date IN ( [January 2012],[February 2012],[March 2012],[April 2012],[May 2012],[June 2012],[July 2012],[August 2012],[September 2012],[October 2012],[November 2012],[December 2012]) ) AS pvt 
    

    • Marked as answer by mp80237 Tuesday, November 27, 2012 3:20 PM
    Monday, November 26, 2012 10:00 PM
    Moderator
  • Hi mp80237

    It seems others have answered already but I decided to paste mine anyways.

    declare @temp table (ticket_number int,impact_date datetime,severity int)
    insert into @temp (ticket_number,impact_date,severity) values
    (327696,'11/25/12 8:00 AM',3),
    (327325,'11/16/12 11:12 PM',3),
    (326511,'11/4/12 8:01 AM',3),
    (326352,'11/1/12 5:00 PM',3),
    (326253,'10/31/12 4:03 PM',3),
    (324647,'10/9/12 11:08 AM',3),
    (324336,'10/4/12 4:30 AM',3),
    (324103,'10/1/12 3:28 PM',3),
    (323942,'9/28/12 6:07 AM',1),
    (323724,'9/25/12 5:00 PM',3),
    (322093,'9/4/12 12:50 PM',3),
    (322306,'9/1/12 8:00 AM',3),
    (321019,'8/18/12 12:04 PM',3),
    (320030,'8/6/12 10:37 AM',4),
    (319901,'8/3/12 8:17 AM',3),
    (319274,'7/23/12 1:33 PM',3),
    (318878,'7/21/12 12:04 AM',3),
    (318979,'7/20/12 10:37 PM',1),
    (318748,'7/19/12 3:04 PM',1),
    (318593,'7/18/12 4:42 AM',3)
    
    select severity, [Jul-12] ,[Aug-12],[Sep-12],[Oct-12],[Nov-12]
    from 
    (
    	select 
    	 [severity]
    	,[ticket_number]
    	,[label] = CONVERT(varchar(100),LEFT(DATENAME(MM,impact_date),3)) + '-' + CONVERT(varchar(2),RIGHT(DATEPART(YY,impact_date),2))
    	from @temp) p
    	pivot (count([ticket_number]) for [label] IN ([Jul-12], [Aug-12], [Sep-12], [Oct-12], [Nov-12])
    ) AS pt
    order by pt.[severity]


    Pérez

    • Marked as answer by mp80237 Tuesday, November 27, 2012 3:20 PM
    Monday, November 26, 2012 10:21 PM