I have a row that looks like this
Year 2006 2007 2008 2009.....etc.
I want a select statement that will get me this
Year
2006
2007
2008
2009
etc.
Try:
Code Snippet
createtable #t (
c1 intnotnull,
c2 intnotnull,
c3 intnotnull,
c4 intnotnull
)
insertinto #t values(2006, 2007, 2008, 2009)
select
case t2.c1
when 1 then t1.c1
when 2 then t1.c2
when 3 then t1.c3
when 4 then t1.c4
endas [Year]
from
#t as t1
crossjoin
(select 1 as [c1] union allselect 2 union allselect 3 union allselect 4)as t2
-- 2005
select
[Year]
from
(select c1, c2, c3, c4from #t)as p
unpivot
([Year] for [c] in(c1, c2, c3, c4))as unpvt
droptable #t
AMB
|||Thanks, but I can't use create table or insert. Appreciate the effort though.|||
WVUProgramer wrote:
Thanks, but I can't use create table or insert. Appreciate the effort though.
You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety.
You just need to use the select part of it (possibly modified to meet your exact requirements)|||Okay, thanks.|||
For the first select, it says there's no from clause in the select statement following the cross join.
In the second one, it says unable to parse text.
I plugged in my field names (which are f1 through f4) and my table name where the t# is
Perhaps i'm confused about the 
and the [Year].
If you copy and paste the script, then execute it, you will see the result is the same you are expecting in your original post.
Can you post the statement you are trying to execute?
AMB
|||select
case t2.F1
when 1 then t1.F1
when 2 then t1.F2
when 3 then t1.F3
when 4 then t1.F4
end as [Year]
from
table1 as t1
cross join
(select 1 as [F1] union all select 2 union all select 3 union all select 4) as t2
My table name is table1 my fields are F1, F2, F3, F4
Error Message: Error in SELECT clause: expression near 'UNION'.
Missing FROM clause.
Unable to parse query text.
Where are you testing the script: (which application)?
AMB
No comments:
Post a Comment