Query Help

Question

Query Help

ncurran217

SSC Eights!

Points: 902
More actions
January 18, 2013 at 9:15 am

#269552

I have a query and it works, but I want to have some of the results go horizontal and not vertical. Here is the query I have currently:
SELECT LISTCODE, YEAR, COUNT(YEAR) AS Count
FROM Names
GROUP BY LISTCODE, RIGHT(LISTCODE, 2), YEAR
HAVING (RIGHT(LISTCODE, 2) = '$List')
ORDER BY LISTCODE, YEAR
Preview of the output of this query is the picture attached name origianl query output.
What I would like is for the query to output is like the picture attached named wantedqueryoutput.
Is this possible in the query or not?

Viewing 4 posts - 1 through 3 (of 3 total)

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

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 1

There's probably a much better/efficient way to do this but here's a simple example you can modify to suit your needs - FYI the U NION ALL is on purpose because our silly proxy things I'm posting some "nasty" code when I paste U+NION :w00t::

DECLARE @Names TABLE (listcode varchar(5), year int)

INSERT INTO @Names

SELECT '003EL', 2005 U NION ALL

SELECT '003EL', 2006 U NION ALL

SELECT '003EL', 2007 U NION ALL

SELECT '004EL', 2005 U NION ALL

SELECT '004EL', 2006 U NION ALL

SELECT '004EL', 2007 U NION ALL

SELECT '004EL', 2008 U NION ALL

SELECT '004EL', 2008

SELECT ListCode, [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012]

FROM (

SELECT LISTCODE, year FROM @Names) AS SourceTable

PIVOT (COUNT(year) FOR year IN ([2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012]) ) AS PivotTable

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

ncurran217 SSC Eights! Points: 902 More actions · Answer 2

That would work but I have a ton of List Codes (i.e. 103EL) and doing that for everyone would not be efficient at all. Thank you though for that option!

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

ncurran217 (1/18/2013)
That would work but I have a ton of List Codes (i.e. 103EL) and doing that for everyone would not be efficient at all. Thank you though for that option!

You don't have to create all the codes as inserts. He posted that because you didn't post any ddl and sample data. The query is against the table that was created. Just do something similar against your real table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/