March 17, 2013 at 7:41 am
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
March 17, 2013 at 10:46 am
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.
March 17, 2013 at 12:19 pm
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
March 17, 2013 at 6:31 pm
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
Change is inevitable... Change for the better is not.
March 17, 2013 at 6:49 pm
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
March 17, 2013 at 8:04 pm
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
Change is inevitable... Change for the better is not.
March 18, 2013 at 1:18 am
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
March 18, 2013 at 12:42 pm
clyde 73016 (3/18/2013)
Hi GuysThx 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 NULLDROP 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
Change is inevitable... Change for the better is not.
March 18, 2013 at 3:53 pm
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
March 18, 2013 at 4:16 pm
Dynamic SQL is definitely NOT required for this problem. We're not pivoting the names of the subdistrict or location type.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply