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.
| Author |
Topic |
|
Jules_S
Starting Member
13 Posts |
 Posted - 2009-05-05 : 08:53:55
|
| Hi allI'm writing a T_SQL statement to transform rows to columns for use in another query. I'm using CASE to do the work - here's a cut-down example:select CASE p.ProductID WHEN 1 THEN UPPER(sd.ProductStocked) END as 'Product1'from FSFdb.dbo.tblSurveyDetail sd join FSFdbWork.dbo.tblK2ProductDataStaging pon sd.ProductId = p.ProductIDThis works OK, but there are two problems. Firstly, I need to create dozens of columns, as there are dozens of product IDs, so the SQL will end up being huge by virtue of needing a CASE for each product ID. The other more major problem for me is that in reality, the product descriptions to be used for the column aliases are quite long, and will take ages to type out.The product descriptions are held in another column of one of the tables used in this query. Is there a way to use the actual value of that column as the column alias? In the above example, what I mean is something like:WHEN 1 THEN UPPER(sd.ProductStocked) END as p.ProductDescriptionSo the column alias comes from the value of another column in the products table. It will save loads of typing and the potential for lots of errors.Any help (including "don't be so lazy and just type them out"!) would be gratefully received.Jules |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-05-05 : 11:12:47
|
| Typing them is one way and probably the fastest. But, you could probably write some dynamic sql to generate the case statement for you by using the INFORMATION_SCHEMA.Columns view. |
 |
|
|
Jules_S
Starting Member
13 Posts |
Posted - 2009-05-06 : 04:07:08
|
| Thanks for the advice - if it's quicker to type it all out then I may have to go down that route - deadlines are looming... :-( |
|
|
|
|
|
|
Column values as column aliases in a CASE?