SQL query

[nikol]

Hi,
I have a SQL Query
select
(case
when a.answer_id is null then b.answer_id
else
a.answer_id
end) as uid,
(
case
when a.val_int is null then b.val_int
else a.val_int
end) as val_int,
(
case
when a.val_date is null then b.val_date
else a.val_date
end) as val_date,
(
case
when a.val_textfield is null then b.val_textfield
else a.val_textfield
end) as val_textfield,
(
case
when a.val_float is null then b.val_float
else a.val_float
end) as val_float
from da_answer_val_unsec a FULL OUTER JOIN
da_answer_val_sec b on a.answer_id=b.answer_id
where (a.answer_id <> 0 or b.answer_id <> 0)

Its running fine giving the output in five different columns as(uid,val_int,val_date,val_textfield & val_date).
But I want the output of five columns in a single column.Is it possible.

 

[r937]

here's how you make 1 column out of 5 --

select 'uid'   [b]as column_type[/b]
     , coalesce(a.answer_id,b.answer_id) 
               [b]as single_column[/b]
  from da_answer_val_unsec a 
full outer 
  join da_answer_val_sec b 
    on a.answer_id=b.answer_id 
 where a.answer_id <> 0 
    or b.answer_id <> 0
union all     
select 'val_int' 
     , coalesce(a.val_int,b.val_int)
  from da_answer_val_unsec a 
full outer 
  join da_answer_val_sec b 
    on a.answer_id=b.answer_id 
 where a.answer_id <> 0 
    or b.answer_id <> 0
union all  
select 'val_date'
     , coalesce(a.val_date,b.val_date)
  from da_answer_val_unsec a 
full outer 
  join da_answer_val_sec b 
    on a.answer_id=b.answer_id 
 where a.answer_id <> 0 
    or b.answer_id <> 0
union all  
select 'val_textfield'
     , coalesce(a.val_textfield,b.val_textfield)
  from da_answer_val_unsec a 
full outer 
  join da_answer_val_sec b 
    on a.answer_id=b.answer_id 
 where a.answer_id <> 0 
    or b.answer_id <> 0
union all  
select 'val_float' as column_type
     , coalesce(a.val_float,b.val_float) 
  from da_answer_val_unsec a 
full outer 
  join da_answer_val_sec b 
    on a.answer_id=b.answer_id 
 where a.answer_id <> 0 
    or b.answer_id <> 0

note: youy get problems if the datatypes are not union compatible -- mysql may do some implicit datatype conversions, better check the output

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)