Select

[sardinka2]

I have the following query . My result is displaying as 1 column and 5 row. I would like to have all results in 1 row and 1 column. How do I do this?
select case answer when 'Yes' then 'Y' Else 'N' end as Custom1 from answers

 

[maswien]

If the results is 5 rows, so you know the 5 values, let's assume they are v1, v2, v3, v4 , v5

So the SQL is :

select t1.col as col1,
t2.col as col2,
t3.col as col3,
t4.col as col4,
t5.col as col5,
from
(select col from MyTable where col = v1) t1,
(select col from MyTable where col = v2) t2,
(select col from MyTable where col = v3) t3,
(select col from MyTable where col = v4) t4,
(select col from MyTable where col = v5) t5

 

[sardinka2]

My results maybe vary from time to time and I would like to have all in one column.
From this:
Y
Y
Y
Y
N
To this: Y Y Y Y N

 

[maswien]

Then I would suggest using temporary table:

create table #temp(id int identity(1,1), col varchar(1))
insert into #temp(answer)
select case answer when 'Yes' then 'Y' Else 'N' end as Custom1 from answers

select t1.col as col1,
t2.col as col2,
t3.col as col3,
t4.col as col4,
t5.col as col5,
from
(select col from #temp where id = 1) t1,
(select col from #temp where id = 2) t2,
(select col from #temp where id = 3) t3,
(select col from #temp where id = 4) t4,
(select col from #temp where id = 5) t5

 

[SQLBill]

Sardinka2,

Just an FYI, this is a column...

1
2
3

This is a row

1 2 3

I'm pretty sure your request "I would like to have all in one column." was a typo since you showed you wanted a row.

-SQLBill

 

[maswien]

If return 1 row 1 column:

create table #temp(id int identity(1,1), col varchar(1))
insert into #temp(answer)
select case answer when 'Yes' then 'Y' Else 'N' end as Custom1 from answers

select t1.col + ' ' +
t2.col + ' ' +
t3.col + ' ' +
t4.col + ' ' +
t5.col as col,
from
(select col from #temp where id = 1) t1,
(select col from #temp where id = 2) t2,
(select col from #temp where id = 3) t3,
(select col from #temp where id = 4) t4,
(select col from #temp where id = 5) t5

 

[ESquared]

Dynamic Cross-Tabs/Pivot Tables
using a Pivot Table

 

[sardinka2]

I added some more conditional statement for my query and I got lost again. I added a tranID number.I need to group each answer for spesific tranID.How do I do this?
drop table #temp
create table #temp(id int identity(1,1), col varchar(1),tranID Numeric )
insert into #temp
select case answer when 'Yes' then 'Y' Else 'N' end as Custom1,tranID
from answers