August 19, 2009 at 7:21 am
Could I get and explanation of how to generate the proper output?
Create table temp (index1 [int] not null,
string1 [nvarchar](50) NOT NULL,
string2 [nvarchar](50) NOT NULL,
boolean1 [tinyint] NOT NULL,
boolean2 [tinyint] NOT NULL)
insert into temp(index1,string1,string2,boolean1,boolean2)
values (1,'','x',1,0)
insert into temp(index1,string1,string2,boolean1,boolean2)
values(2,'y','yy',1,0)
insert into temp(index1,string1,string2,boolean1,boolean2)
values(2,'','z',0,1)
insert into temp(index1,string1,string2,boolean1,boolean2)
values(2,'','a',0,0)
insert into temp(index1,string1,string2,boolean1,boolean2)
values(3,'','b',0,0)
I want my output to be
indexvaluevalue
1 x
2 y z
if boolean1 is 1 then output string1 unless its '' then output string2
if boolean2 is 1 then output string1 unless its '' then output string2
if both booleans are 0 no results for that index
Thanks
August 19, 2009 at 7:59 am
We are happy to help, but we don't like to do work for you. This looks like an exam or interview question, and in that case, you should be trying to do this yourself.
If you make an attempt, we will be happy to try and help.
August 19, 2009 at 12:01 pm
LOL.
It is neither, but I definitely understand the point.
Here is my solution.
It just seems convoluted.
select temp1.index1,
case temp1.boolean1
when 1 then temp1.string1
else ''
end,
case isnull(temp2.string2,'0') -- value is on the left half of join
when '0' then case temp1.boolean2
when 1 then temp1.string2
else ''
end
else temp2.string2
end
from temp as temp1
left outer join temp as temp2
on temp2.index1 = temp1.index1 --only rows with same index1
--only rows with different booleans
and temp2.boolean1 temp1.boolean1
and not (temp2.boolean1 = 0 and temp2.boolean2=0)
and temp2.boolean2 temp1.boolean2
where (temp1.boolean1 = 1 and temp2.boolean2 is null) or --only boolean1 true
(temp1.boolean2 = 1 and temp2.boolean2 is null) or --only boolean2 true
(temp1.boolean1 = 1 and temp2.boolean2 = 1) --only one row when both booleans true
I'd appreciate a review of this and any suggestion.
Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply