Subquery

[sardinka2]

I have the following code where I have some subquery. However my subquery return more than 1 row. Is it some how I can put all subquery result in 1 row instead of couple?
select Custom1=(select case Answer when 'Yes' then 'Y' else 'N' end + ' '
fromAnswers wa where wa.tranid=wt.TranID ),
....
FROM trans wt ....
more joins

 

[kavin]

I think u can use the sum() for the case,as follows

sum(select case Answer when 'Yes' then 1 else 0 end + ' '
-----
-----))
...
...
more joins

 

[sardinka2]

Not really. I need to display all Yes and No. Not just a sum

 

[vongrunt]

Use TOP 1 and optionally ORDER BY in subquery. But...

What if there are two records for the same TranID, one with Answer = 'Yes', another with 'No'. Which one is correct?

 

[sardinka2]

I have 7 answers with possible combinations 'Yes' and 'No'.
Maybe temp tamp will work? In a temp i will need to have the data store like this:
Answer TranID
Y Y Y .. 123
can you help me write this?