Pivot Query with Count

  • Hi there

    I am fairly new to SQL Server TSQL and I have a strong MS Access background.

    I can't get my head around pivot queries is TSQL.

    I have 3 Colums with data in it:

    1) City

    2) Delivery_type

    3) Date_Delivered

    I want to create a pivot query that has the format with 14 columns

    1) City (Grouped)

    2) Deliver (Grouped)

    3) Jan (Count the amount of dates for month of Jan)

    4) Feb (Count the amount of dates for month of Feb)

    ...

    14) Dec (Count the amount of dates for month of Feb)

    If you can guide me in the right direction I will appreciate it, Thx

  • Welcome to SSC. First, you did describe your problem okay but we really could use more information to really help you. For myself, and others, it helps to actually see what you are trying to accomplish. To do this it would help if you could provide the DDL (CREATE TABLE statement) for the table involved, provide some sample (not real) data that is representative of your problem domain as a series of INSERT INTO statements, the expected results based on the sample data (what you would like to see as the result set), and the code you have written so far in an attempt to solve your problem.

    For help with this, please read the first article I reference below in my signature block regarding asking for help, it will walk you through what you need to post and how to do it. Once we have all of this, I am sure you will get great help for several people.

  • Hi

    Here's a couple of articles by Jeff Moden on the subject

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    They should help

  • PIVOT in SQL Server is a virtual cripple compared to what it is in MS Access. It's also usually slower than some traditional methods. See the first article that MickyT listed for more on that.

    Here's some test data. You should always do that when asking a question so people will respond more quickly and you'll get tested answers. Se the first link in my signature line below for more info on that.

    SELECT TOP 1000000

    City = 'City' + RIGHT('00'+ CAST(ABS(CHECKSUM(NEWID()))%100+1 AS VARCHAR(10)),3),

    Delivery_Type = (SELECT CASE N WHEN 0 THEN 'Standard' WHEN 1 THEN '2 Day' ELSE 'OverNight' END FROM (SELECT N = ABS(CHECKSUM(NEWID()))%3)d),

    Date_Delivered = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'Jan 2010','Mar 2013'),'Jan 2010')

    INTO #TestData

    FROM master.sys.all_columns ac1

    CROSS JOIN master.sys.all_columns ac2

    ;

    Here's one solution.

    WITH

    ctePreAgg AS

    ( --=== Preaggregate the data for an improvement in performance

    SELECT Year = DATEPART(yy,Date_Delivered),

    Month = DATEPART(mm,Date_Delivered),

    City,

    Deliver = Delivery_Type,

    MonthCount = COUNT(*)

    FROM #TestData--dbo.YourTable --<---<<<< LOOK! You'll need to change this!

    GROUP BY DATEPART(yy,Date_Delivered),DATEPART(mm,Date_Delivered),City,Delivery_Type

    ) --=== Now, pivot the data using a high performance crosstab.

    SELECT City,

    Year,

    Deliver,

    [Jan] = SUM(CASE WHEN Month = 1 THEN MonthCount ELSE 0 END),

    [Feb] = SUM(CASE WHEN Month = 2 THEN MonthCount ELSE 0 END),

    [Mar] = SUM(CASE WHEN Month = 3 THEN MonthCount ELSE 0 END),

    [Apr] = SUM(CASE WHEN Month = 4 THEN MonthCount ELSE 0 END),

    [May] = SUM(CASE WHEN Month = 5 THEN MonthCount ELSE 0 END),

    [Jun] = SUM(CASE WHEN Month = 6 THEN MonthCount ELSE 0 END),

    [Jul] = SUM(CASE WHEN Month = 7 THEN MonthCount ELSE 0 END),

    [Aug] = SUM(CASE WHEN Month = 8 THEN MonthCount ELSE 0 END),

    [Sep] = SUM(CASE WHEN Month = 9 THEN MonthCount ELSE 0 END),

    [Oct] = SUM(CASE WHEN Month = 10 THEN MonthCount ELSE 0 END),

    [Nov] = SUM(CASE WHEN Month = 11 THEN MonthCount ELSE 0 END),

    [Dec] = SUM(CASE WHEN Month = 12 THEN MonthCount ELSE 0 END),

    [YearTotal] = SUM(MonthCount)

    FROM ctePreAgg

    GROUP BY City, Year, Deliver

    ORDER BY City, Year, Deliver

    ;

    I added a YEAR column and a YearTotal column. Change as you wish.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It can get a little tedious typing out all the months so I only did Jan,Feb,March and Dec. A dynamic pivot would be usefully if you are uncertain of the number of columns the pivot should generate.

    Pivot is pretty Straight forward.

    The Inner Query Should Contain the columns you wish to display as well as the column to be aggregated. the keyword PIVOT is used after and then the aggregation (COUNT(DATE_DEL)) should proceed after. after that is use the key word for and the column containing the values you which to display as columns.

    The Outer Query should contain all the columns you wish to display as the datasheet retrieval.

    CREATE TABLE #Pivot_exp

    (

    City varchar(45),

    Delivery_type varchar(10),

    Date_Del varchar(100)

    )

    insert into #Pivot_exp

    VALUES ('LaSalle','Mail','Jan'),

    ('Peru','Snail Mail','Jan'),

    ('Lombard','E- Mail','Jan'),

    ('Lombard','E- Mail','Jan'),

    ('Lombard','E- Mail','Feb'),

    ('Lombard','E- Mail','Mar'),

    ('Lombard','E- Mail','DEC'),

    ('OakBrook','E- Mail','DEC')

    SELECT City,Delivery_type,[Jan],[Feb],[Mar],[Dec]

    FROM( SELECT City,Delivery_type,Date_Del

    FROM #Pivot_exp)PVT

    PIVOT

    (

    COUNT(DATE_DEL)

    FOR DATE_DEL IN ([Jan],[Feb],[Mar],[Dec])

    )AS PRT

  • raym85 (3/17/2013)


    It can get a little tedious typing out all the months...

    Pivot is pretty Straight forward.

    I agree. That's another reason why I don't use pivot. You have to type the months and then copy and paste. If you want a total, it gets even more complicated.

    I extended the pivot code to include all months and made the necessary change (Convert the date to 3 letter month) to make it work against the million row table I generated for this test in my previous post. Compare the two for performance. Preaggregation will make it faster but it still won't be faster than the preaggregated cross tab.

    SELECT City,Delivery_type,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

    FROM( SELECT City,Delivery_type,Date_Del = CONVERT(CHAR(3),Date_Delivered,107)

    FROM #TestData)PVT

    PIVOT

    (

    COUNT(DATE_DEL)

    FOR DATE_DEL IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

    )AS PRT

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Guys

    Thx for the responses. I have the following data

    SUB_DISTRICT ... LOCATION_TYPE ... Date

    Paris Inland 2012/1/1

    Nigeria Inland 2012/1/2

    Brasil Coast 2012/6/3

    Paris Inland 2012/2/2

    Nigeria Inland 2012/5/5

    Brasil Coast 2012/6/3

    Texas Inland 2012/12/12

    Paris Inland 2012/11/11

    Nigeria Mountain 2012/8/8

    Nigeria Mountain 2012/10/10

    Texas Inland 2012/10/12

    Texas Inland 2012/9/12

    Texas Inland 2012/1/1

    the ultimate result should be

    SUB_DISTRICT LOCATION_TYPE Q1 Q2 Q3 Q4

    Paris Inland 2 0 0 1

    Nigeria Inland 1 1 0 0

    Nigeria Mountain 0 0 0 2

    Brasil Coast 0 0 1 0

    Texas Inland 1 0 0 3

    I have tried creating the T-SQL but to no avail. Can you guide me in the right direction pls

    IF OBJECT_ID('TempDB..#Results') IS NOT NULL

    DROP TABLE #Results

    CREATE TABLE #Results (District VARCHAR(50), Location varchar(50), [Q1] INT,[Q2] INT, [Q3] INT,[Q4] INT)

    insert into #Results

    select District, Location,[Q1],[Q2],[Q3],[Q4] from

    (

    SELECT SUB_DISTRICT, LOCATION_TYPE, Q = DATEPART(QUARTER, REGISTERED_DATE)

    FROM Tata.dbo.Tbl_results_tabdel

    WHERE (TB_RESULT_TYPE IN (N'DIRECT', N'DIRECTP', N'CULAUR'))

    ) SRC

    Pivot (count(Q)

    for Q in ([Q1],[Q2],[Q3],[Q4])

    ) as PVT

    SELECT * FROM #Results

  • clyde 73016 (3/18/2013)


    Hi Guys

    Thx for the responses. I have the following data

    SUB_DISTRICT ... LOCATION_TYPE ... Date

    Paris Inland 2012/1/1

    Nigeria Inland 2012/1/2

    Brasil Coast 2012/6/3

    Paris Inland 2012/2/2

    Nigeria Inland 2012/5/5

    Brasil Coast 2012/6/3

    Texas Inland 2012/12/12

    Paris Inland 2012/11/11

    Nigeria Mountain 2012/8/8

    Nigeria Mountain 2012/10/10

    Texas Inland 2012/10/12

    Texas Inland 2012/9/12

    Texas Inland 2012/1/1

    the ultimate result should be

    SUB_DISTRICT LOCATION_TYPE Q1 Q2 Q3 Q4

    Paris Inland 2 0 0 1

    Nigeria Inland 1 1 0 0

    Nigeria Mountain 0 0 0 2

    Brasil Coast 0 0 1 0

    Texas Inland 1 0 0 3

    I have tried creating the T-SQL but to no avail. Can you guide me in the right direction pls

    IF OBJECT_ID('TempDB..#Results') IS NOT NULL

    DROP TABLE #Results

    CREATE TABLE #Results (District VARCHAR(50), Location varchar(50), [Q1] INT,[Q2] INT, [Q3] INT,[Q4] INT)

    insert into #Results

    select District, Location,[Q1],[Q2],[Q3],[Q4] from

    (

    SELECT SUB_DISTRICT, LOCATION_TYPE, Q = DATEPART(QUARTER, REGISTERED_DATE)

    FROM Tata.dbo.Tbl_results_tabdel

    WHERE (TB_RESULT_TYPE IN (N'DIRECT', N'DIRECTP', N'CULAUR'))

    ) SRC

    Pivot (count(Q)

    for Q in ([Q1],[Q2],[Q3],[Q4])

    ) as PVT

    SELECT * FROM #Results

    It's your turn, Clyde. We've already asked you to provide future test data in a readily consumable format. Please see the first link in my signature line below and we'll be happy to assist.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tsk tsk.

    I had some time after work to help ya out. Honestly the best way to go is dynamic sql. I gave you an explaination about piviot so you should be good on how to use it. Dyanmic sql is a beast of its own though, hope you're able to swap out the tables without to much trouble

    CREATE TABLE #TEMP

    (

    SUB_DISTRICT varchar(100) ,

    LOCATION_TYPE varchar(100),

    Date_type date

    )

    INSERT INTO #TEMP

    VALUES('Paris', 'Inland', '2012/1/1'),

    ('Nigeria', 'Inland', '2012/1/2'),

    ('Brasil', 'Coast', '2012/6/3'),

    ('Paris', 'Inland', '2012/2/2'),

    ('Nigeria', 'Inland', '2012/5/5'),

    ('Brasil', 'Coast', '2012/6/3'),

    ('Texas', 'Inland', '2012/12/12'),

    ('Paris', 'Inland', '2012/11/11'),

    ('Nigeria', 'Mountain', '2012/8/8'),

    ('Nigeria', 'Mountain', '2012/10/10'),

    ('Texas', 'Inland', '2012/10/12'),

    ('Texas', 'Inland', '2012/9/12'),

    ('Texas', 'Inland', '2012/1/1')

    DECLARE @cols10 VARCHAR(2000)

    SELECT @cols10 = COALESCE(@cols10 + ',[' + 'Q' + DATE_TYPE + ']','[' + 'Q' + DATE_TYPE + ']')

    FROM (

    SELECT DISTINCT CONVERT(VARCHAR(10),(DATEPART(Quarter,DATE_TYPE))) DATE_TYPE

    FROM #TEMP

    GROUP BY DATE_TYPE)LA

    DECLARE @SQL10 VARCHAR(4000)

    SET @SQL10 = '

    SELECT SUB_DISTRICT,

    LOCATION_TYPE,

    '+ @cols10 +'

    FROM (SELECT

    SUB_DISTRICT,

    LOCATION_TYPE,

    ''Q''+ CONVERT(VARCHAR(10),(DATEPART(Quarter,DATE_TYPE)))DATE_TYPE

    FROM #TEMP) AS A

    PIVOT (COUNT(DATE_type) FOR DATE_type IN ('+ @cols10 +') )P'

    exec (@SQL10)

    print @SQL10

  • Dynamic SQL is definitely NOT required for this problem. We're not pivoting the names of the subdistrict or location type.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply