Question about Pivot / CrossTabs...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question about Pivot / CrossTabs...

Author

Topic

mr.sean.r
Starting Member

5 Posts

Posted - 2008-08-22 : 10:17:50

I have done quite a bit of searching here and haven't been able to adapt any of the suggestions to my situation...

I have a table that looks like this


Whse    Date            #Invoices       #Changed Invoices
15	8/21/2008	4674		3174
35	8/21/2008	3347		1912
50	8/21/2008	5083		1489
30	8/21/2008	4938		2807
18	8/21/2008	3549		2502
11	8/21/2008	888		791
35	8/22/2008	3705		2143
30	8/22/2008	5457		3154
18	8/22/2008	3848		2717
11	8/22/2008	935		836
40	8/22/2008	5480		3514

My desired output would look like this


	8/21/2008		8/22/2008
Whse	#inv	#Changed	#inv   	#Changed
11	888	791		935	836
15	4674	3174		0	0
40	0	0		5480	3514
30	4938	2807		5457	3154

etc...

Is this possible???

I am fairly new to SQL and really new to Pivot/Crosstab, so be a little easy on me...

Thanks,
Sean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 10:21:33

If you're looking for getting this data on some reports, it will be very easy to get this formatting in your reporting application. For example, in sql reporting services you just need a matrix with row group as Whse and column group as Date to get the required format.

mr.sean.r
Starting Member

5 Posts

Posted - 2008-08-22 : 10:41:39

Sorry I forgot that part,

I will be displaying this information on a web page, using .aspx pages.

I've never used Sql Reporting Services.

Thanks for the suggestion,
Sean

mr.sean.r
Starting Member

5 Posts

Posted - 2008-08-25 : 16:29:07

Any other suggestions?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-25 : 17:04:08

Look into Dynamic Cross Tab queries. There's a ton of articles if you do a google search.

Kobojunkie
Starting Member

8 Posts

Posted - 2008-08-25 : 17:30:55

I don't believe in this case you need to PIVOT... you simply need to group by Date

This link

http://www.asp.net/learn/sql-videos/

Will help you understand basics of reporting. What a PIVOT does is help you turn rows into columns which does not seem to be what your result is about.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 23:07:30

[code]DECLARE @sample TABLE
(
[Whse] int,
[Date] datetime,
[#Invoices] int,
[#Changed Invoices] int
)

INSERT INTO @sample
SELECT 15, '20080821', 4674, 3174 UNION ALL
SELECT 35, '20080821', 3347, 1912 UNION ALL
SELECT 50, '20080821', 5083, 1489 UNION ALL
SELECT 30, '20080821', 4938, 2807 UNION ALL
SELECT 18, '20080821', 3549, 2502 UNION ALL
SELECT 11, '20080821', 888, 791 UNION ALL
SELECT 35, '20080822', 3705, 2143 UNION ALL
SELECT 30, '20080822', 5457, 3154 UNION ALL
SELECT 18, '20080822', 3848, 2717 UNION ALL
SELECT 11, '20080822', 935, 836 UNION ALL
SELECT 40, '20080822', 5480, 3514

-- USING CASE WHEN
SELECT s.[Whse],
SUM(CASE WHEN s.[Date] = '20080821' THEN [#Invoices] ELSE 0 END) AS [20080821_#Inv],
SUM(CASE WHEN s.[Date] = '20080821' THEN [#Changed Invoices] ELSE 0 END) AS [20080821_#Changed],
SUM(CASE WHEN s.[Date] = '20080822' THEN [#Invoices] ELSE 0 END) AS [20080822_#Inv],
SUM(CASE WHEN s.[Date] = '20080822' THEN [#Changed Invoices] ELSE 0 END) AS [20080822_#Changed]
FROM @sample s
GROUP BY s.[Whse]
ORDER BY s.[Whse]

-- USING PIVOT
SELECT r1.[Whse], r1.[20080821_#Inv], r2.[20080821_#Changed], r1.[20080822_#Inv], r2.[20080822_#Changed]
FROM
(
SELECT [Whse], ISNULL([20080821], 0) AS [20080821_#Inv], ISNULL([20080822], 0) AS [20080822_#Inv]
FROM
(
SELECT [Whse], [Date], [#Invoices]
FROM @sample
) s1
pivot
(
SUM([#Invoices])
FOR [Date] IN ([20080821], [20080822])
) p1
) r1
INNER JOIN
(
SELECT [Whse], ISNULL([20080821], 0) AS [20080821_#Changed], ISNULL([20080822], 0) AS [20080822_#Changed]
FROM
(
SELECT [Whse], [Date], [#Changed Invoices]
FROM @sample
) s2
pivot
(
SUM([#Changed Invoices])
FOR [Date] IN ([20080821], [20080822])
) p2
) r2 ON r1.[Whse] = r2.[Whse]
ORDER BY r1.[Whse]

/* RESULT

Whse 20080821_#Inv 20080821_#Changed 20080822_#Inv 20080822_#Changed
----------- ------------- ----------------- ------------- -----------------
11 888 791 935 836
15 4674 3174 0 0
18 3549 2502 3848 2717
30 4938 2807 5457 3154
35 3347 1912 3705 2143
40 0 0 5480 3514
50 5083 1489 0 0

(7 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-26 : 02:01:42

mr.sean.r,

Do the solutions that are provided work, or are you looking to create the cross tab based of values in your table that are not pre-determined (i.e. not hard-coding the dates). If you need to dynamically create the cross tab, I think I have a article which explains it (I just need to dig a little). Let me know.

mr.sean.r
Starting Member

5 Posts

Posted - 2008-08-26 : 12:15:47

I am working on implimenting the above solutions, I will respond tomorrow.

Thanks for the help...

mr.sean.r
Starting Member

5 Posts

Posted - 2008-08-27 : 09:21:54

Thanks for all of the help...

I ended up using the below code from khtans' post. I modified it a little.


SELECT	s.[Whse], 
	SUM(CASE WHEN s.[Date] = 'CURSORDATE' THEN [#Invoices] ELSE 0 END)		AS	[CURSORDATE_#Inv],
	SUM(CASE WHEN s.[Date] = 'CURSORDATE' THEN [#Changed Invoices] ELSE 0 END)	AS	[CURSORDATE_#Changed]
FROM	@sample s
GROUP BY s.[Whse]
ORDER BY s.[Whse]

I then, for the sake of automation, i wrapped it in a cursor to loop through the last 5 dates in the table...

Giving me my desired output...

Thanks again for the help!!!
Sean

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 09:44:59

if you always need the last 5 dates data, you can try this

SELECT	s.[Whse], 
	SUM(CASE WHEN s.row_no = 1 THEN [#Invoices] 		ELSE 0 END)	AS	[date1_#Inv],
	SUM(CASE WHEN s.row_no = 1 THEN [#Changed Invoices] 	ELSE 0 END)	AS	[date1_#Changed],
	SUM(CASE WHEN s.row_no = 2 THEN [#Invoices] 		ELSE 0 END)	AS	[date2_#Inv],
	SUM(CASE WHEN s.row_no = 2 THEN [#Changed Invoices] 	ELSE 0 END)	AS	[date2_#Changed],
	SUM(CASE WHEN s.row_no = 3 THEN [#Invoices] 		ELSE 0 END)	AS	[date3_#Inv],
	SUM(CASE WHEN s.row_no = 3 THEN [#Changed Invoices] 	ELSE 0 END)	AS	[date3_#Changed],
	SUM(CASE WHEN s.row_no = 4 THEN [#Invoices] 		ELSE 0 END)	AS	[date4_#Inv],
	SUM(CASE WHEN s.row_no = 4 THEN [#Changed Invoices] 	ELSE 0 END)	AS	[date4_#Changed],
	SUM(CASE WHEN s.row_no = 5 THEN [#Invoices] 		ELSE 0 END)	AS	[date5_#Inv],
	SUM(CASE WHEN s.row_no = 5 THEN [#Changed Invoices] 	ELSE 0 END)	AS	[date5_#Changed]
FROM	(
		SELECT	[Whse], [Date], [#Invoices], [#Changed Invoices],
			row_no	= row_number() OVER(PARTITION BY [Whse] ORDER BY [Date] DESC)
		FROM	@sample 
	) s
WHERE	s.row_no	<= 5
GROUP BY s.[Whse]
ORDER BY s.[Whse]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources