Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot Query with Count Expand / Collapse
Author
Message
Posted Sunday, March 17, 2013 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 5:39 AM
Points: 2, Visits: 18
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
Post #1431982
Posted Sunday, March 17, 2013 10:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:22 PM
Points: 20,680, Visits: 32,279
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432003
Posted Sunday, March 17, 2013 12:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:08 PM
Points: 1,027, Visits: 3,085
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
Post #1432006
Posted Sunday, March 17, 2013 6:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432052
Posted Sunday, March 17, 2013 6:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
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
Post #1432054
Posted Sunday, March 17, 2013 8:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432069
Posted Monday, March 18, 2013 1:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 5:39 AM
Points: 2, Visits: 18
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

Post #1432091
Posted Monday, March 18, 2013 12:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432308
Posted Monday, March 18, 2013 3:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
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
Post #1432369
Posted Monday, March 18, 2013 4:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:46 PM
Points: 35,218, Visits: 31,676
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432382
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse