Pivot or crosstab question. I am lost!
-
Hi, I am trying to figure out the best way to get sql to return me some data in a report format. I have the following raw data
Year Groupp Zip
---------------------------
2009 A 11111
2009 A 11111
2009 A 11111
2009 B 33333
2010 A 11111
2010 A 11111
2010 B 11111
I would like a report to look like this that returns to me the number of occurances for each condition:
Year Zip A B
--------------------------------
2009 11111 3 0
2009 33333 0 1
2010 11111 2 1
Can this be done easily? Also, can it be done dynamically because the table names and columns names might change.
Thanks for any help/suggestions.
CREATE TABLE #Table1
(Year varchar(4),
Zip varchar(5),
Groupp varchar(1)
)
GO
INSERT INTO #Table1
(Year, Zip, Groupp)
SELECT '2009', '11111', 'A'
UNION ALL
SELECT '2009', '11111', 'A'
UNION ALL
SELECT '2009', '11111', 'A'
UNION ALL
SELECT '2009', '33333', 'B'
UNION ALL
SELECT '2010', '11111', 'A'
UNION ALL
SELECT '2010', '11111', 'A'
UNION ALL
SELECT '2010', '11111', 'B'
GO
-
Hi,
Please refer the article written by jeff - Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Link : http://www.sqlservercentral.com/articles/T-SQL/63681/
ARUN SAS
-
SELECT Year,Zip,
SUM(CASE WHEN Groupp='A' THEN 1 ELSE 0 END) AS 'A',
SUM(CASE WHEN Groupp='B' THEN 1 ELSE 0 END) AS 'B'
FROM #Table1
GROUP BY Year,Zip
ORDER BY Year,Zip
If the possible values in 'Groupp' aren't known in advance you'll have to build the SQL above dynamically.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply