Fulgent
asked on
SQL Question
Say I have a table like this
Pcode Val1 Val2
A 1 2
B 1 NULL
C 9 14
What SQL would I need to turn it into a single column table like this?
A1
A2
B1
C9
C14
Pcode Val1 Val2
A 1 2
B 1 NULL
C 9 14
What SQL would I need to turn it into a single column table like this?
A1
A2
B1
C9
C14
if not
select cast(a as varchar)+val1 col from mytable
union
select cast(a as varchar)+val2 col from myTable
select cast(a as varchar)+val1 col from mytable
union
select cast(a as varchar)+val2 col from myTable
what do you want to do if val1 and val2 is same for row 1?
if you want 2 values then use union all
select cast(a as varchar)+val1 col from mytable
union all
select cast(a as varchar)+val2 col from myTable
if you want 2 values then use union all
select cast(a as varchar)+val1 col from mytable
union all
select cast(a as varchar)+val2 col from myTable
oops, you want to ignore nulls
select cast(a as varchar)+val1 col from mytable where val1 is not null
union all
select cast(a as varchar)+val2 col from myTable where val2 is not null
select cast(a as varchar)+val1 col from mytable where val1 is not null
union all
select cast(a as varchar)+val2 col from myTable where val2 is not null
try:
select pcode + cast(val1 as nvarchar(10)) as mynewcolumn from mytesttable where val1 is not null
union
select pcode + cast(val2 as nvarchar(10)) as mynewcolumn from mytesttable where val2 is not null
select pcode + cast(val1 as nvarchar(10)) as mynewcolumn from mytesttable where val1 is not null
union
select pcode + cast(val2 as nvarchar(10)) as mynewcolumn from mytesttable where val2 is not null
ASKER
Thanks for the replies, I forgot to mention that the number of columns is dynamic, can be anything up to 99 columns
up to 99 columns? give a sample for more than 2 columns...
something like this?
Pcode Val1 Val2 Val3 ... Val99
A 1 2 NULL... 3
B 1 NULL 5......... 6
C 9 14 NULL... 1
something like this?
Pcode Val1 Val2 Val3 ... Val99
A 1 2 NULL... 3
B 1 NULL 5......... 6
C 9 14 NULL... 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select a+val1 col from mytable
union
select a+val2 col from myTable
assuming they all are varchar