Generate an excel like report from SQL

Question

I am novice to SQL and I have two tables Ticket and TicketAttributes with following Schema

CREATE TABLE [dbo].[Ticket](
   [TicketID] [int] IDENTITY(1,1) NOT NULL, --Primary key
   [Category] [varchar](256) NOT NULL,
   [Description] [varchar](256) NULL,
   [LibID] [int] NOT NULL,
   [Status] [smallint] NULL,
   [LogID] [int] NULL)

Ticket Attributes

CREATE TABLE [dbo].[TicketAttributes](
    [TicketID] [int] NOT NULL,
    [TicketAttrID] [int] IDENTITY(1,1) NOT NULL,
    [AttributeID] [int] NOT NULL,
    [AttributeGroup] [varchar](255) NULL,
    [AttributeValue] [nvarchar](max) NULL,
    [Status] [smallint] NULL,
    [LogID] [int] NULL)

Where Ticket Attribute is another table that stores different attributes of a ticket like TicketStatus, TicketCategory etc.. Now I need to generate a report that looks like

                   TicketStatus1     TicketStatus 2     TicketStatus3
    -----------------------------------------------------------------
TicketCategory1         7                3
Ticketcategory2                          4
TicketCategory3                                            8

I want to see the count of each of the status of each ticket category. For Eg:- I have the following Data in TicketTable

----------------------------------------------
TicketID  Name         Price        Date
------------------------------------------------
155       Ticket4      $20     16 Jan 2016
157       Ticket3      $300    17 Jan 2016
158       Ticket1      $100    18 Jan 2016
159       Ticket2      $500    19 Jan 2016

Now in the TicketAttribute Table

----------------------------------------------
    TicketID  AttributeID  AttributeValue
    ------------------------------------------------
    155       500             Joe     
    155       600             Reserved    
    155       700             Economy
    155       800             San Jose

where AttributeIDs

 500=Nameofthe Passenger
 600= Status of Ticket
 700= Class
 800= Destination

Now lets say I want to see what is the count of number of active tickets in each of the class per status

               Booked     Cancelled     PaymentPending  ............
    -----------------------------------------------------------------
Economy         7                3
Economy Plus                     4
Business                                         8

Hope I am clear now. how to go about this using SQL Query

How about changing the results to display in Text instead of to Grid https://www.mssqltips.com/sqlservertip/2346/different-options-for-query-results-in-sql-server-management-studio/ — Mark Kram, Nov 10 '16 at 22:50
Yeah, anything works..but my main question is how to go about writing a query for this — Programmerzzz, Nov 10 '16 at 22:52
Just posted, pls check and let me know if you need more info — Programmerzzz, Nov 10 '16 at 23:19
I definitely fail to see how is displaying results as Text relevant to pivoting data......... — Matt, Nov 11 '16 at 00:05
The columns in your sample data do NOT match the column names. — Mark Kram, Nov 11 '16 at 01:40

score 2 · Accepted Answer · answered Nov 11 '16 at 00:00

2

USING PIVOT

;WITH cte AS (
    SELECT
       c.AttributeValue as Class
       ,s.AttributeValue as StatusOfTicket
    FROM
       Ticket t
       LEFT JOIN TicketAttributes c
       ON t.TicketId = c.TicketId
       AND c.AttributeID = 700
       LEFT JOIN TicketAttributes s
       ON t.TicketId = s.TicketId
       AND s.AttributeID = 600
)

SELECT *
FROM
    cte
    PIVOT (
       COUNT(StatusOfTicket) FOR StatusOfTicket IN (Reserved,Cancelled,PaymentPending)
    ) p

USING Conditional Aggregation:

SELECT
    c.AttributeValue as Class
    ,COUNT(DISTINCT CASE WHEN s.AttributeValue = 'Reserved' THEN c.TicketId END) as Reserved
    ,COUNT(DISTINCT CASE WHEN s.AttributeValue = 'Cancelled' THEN c.TicketId END) as Cancelled
    ,COUNT(DISTINCT CASE WHEN s.AttributeValue = 'PaymentPending' THEN c.TicketId END) as PaymentPending
FROM
    Ticket t
    LEFT JOIN TicketAttributes c
    ON t.TicketId = c.TicketId
    AND c.AttributeID = 700
    LEFT JOIN TicketAttributes s
    ON t.TicketId = s.TicketId
    AND s.AttributeID = 600
GROUP BY
    c.AttributeValue

answered Nov 11 '16 at 00:00

Matt

13,833
2
16
28

What if the Values of TicketStatuses like Reserved,Cancelled,PaymentPending are dynamic. Meaning, they are not predefined but stored as attributeValues in the Table – Programmerzzz Nov 11 '16 at 00:03
@Programmerzzz I would have to guess there are some limited number of statuses? In the case of staying dynamic, you would dynamically and look up the DISTINCT values of Status would be and, and then modify this SQL and execute the dynamic sql – Matt Nov 11 '16 at 00:08
@Programmerzzz http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Matt Nov 11 '16 at 00:09
Thanks looks like the second one works but can you help me understand that – Programmerzzz Nov 11 '16 at 00:15
@Programmerzzz both will work, For both you need to make a table that has multiple columns 1 for Class and 1 for Status to do that you need to left join tickets to the attribute table twice. Use a left join in case an attribute is missing. Then you simply do aggregation. For aggregations such as SUM(), MAX(), COUNT() they will all ignore null values so by using a case expression to remove values when the condition is not met and then aggregate it you limit to counting items of that condition. – Matt Nov 11 '16 at 15:53

Generate an excel like report from SQL

1 Answers1