Read table values into a query

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

General SQL Server Forums

New to SQL Server Programming

Read table values into a query

Author

Topic

Olga Likhodi
Starting Member

1 Post

Posted - 2011-04-13 : 15:06:12

Hello,
I have to unpivot a table using this query for example:

SELECT salesRep, [Month], Sale
FROM (
SELECT salesRep, Jan, Feb, Mar
FROM @Sales ) p
UNPIVOT (
Sale FOR [Month] IN ([Jan], [Feb], [Mar])
)AS unpvt ;

I have a long list of columns to unpivot and it seems unreasonable to type the list every time. Can I have the list in a separate table and read the values from that table into my query?
Thank you,
Olga

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-13 : 15:31:04

Even if you have a long list, as long as the list is fixed (and unless it is like 20,000 long) I would say copy the list and do it just like you did. Now, if the list is not be known in advance, then you need to use dynamic pivoting.

Madhivanan's page here (http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx) has the description of how to do dynamic pivoting. It is really pretty simple - you copy and install his stored procedure and then just run the stored procedure with 3 parameters.

But I still would say, if you can avoid the dynamic pivoting, do it using the static pivoting that you are already doing.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-13 : 15:31:52

SELECT salesRep, [Month], Sale
FROM (
SELECT salesRep, [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]
FROM @Sales ) p
UNPIVOT (
Sale FOR [Month] IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
)AS unpvt ;

Really? Is that a lot of typing?

You can build some of it using the INFORMATION_SCHEMA.COLUMNS view:

SELECT quotename(column_name) + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='myTable'

This won't work for table variables though, and gets tricky when using temp tables.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-13 : 17:31:33

Do you have a report writer? SQL was never meant to do this kind of work. PIVOT is a really awful kludge to RDBMS people.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-13 : 17:38:55

quote:
Do you have a report writer? SQL was never meant to do this kind of work.

Pretty sure most report writers don't do UNPIVOT either.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources