September 22, 2011 at 10:16 am
Hi,
I am having data in 2 tables in the following format and I should get data in a single row with out duplicate eid's.
Incase if there is no data on one column(ex:estatus), it should be populated as null
Please correct my query without having duplicate rows and should populate null incase of no data with particular value.
Following is the sample format:
--===== Create the test table with
CREATE TABLE #mytable1
(eid numeric,fname varchar(10),lname varchar(10))
create table #mytable2
(eid numeric,estatus varchar(10),points numeric)
--inserting data into 1st table (#mytable1)
insert into #mytable (eid,fname,lname)
select '1', 'abc','def' union all
select '2', 'zzz','yyy' union all
select '3', 'ada','sda' union all
select '4', 'wqw','aas' union all
select '5', 'sga','sds' union all
select '6', 'ttt','eee'
--inserting data into 2nd table (#mytable2)
insert into #mytable2 (eid,estatus,points)
select '1', 'good',100 union all
select '1', 'verygood',200 union all
select '2', 'good',300 union all
select '2', 'verygood',400 union all
select '3', 'good',100 union all
select '3', 'verygood',300 union all
select '4', 'good',500 union all
select '4', 'verygood',500 union all
select '5', 'good',600 union all
select '6', 'verygood',700
Query:
select a.eid,a.fname,a.lname,b.points
from #mytable1 a
inner join #mytable2 b
on a.eid=b.eid
where b.estatus in ('good','verygood')
Expecting Result:
select 1 as eid,'abc'as fname,'def' as lname,100 as points1,200 as points2 union all
select 2 as eid,'zzz'as fname,'yyy' as lname,300 as points1,400 as points2 union all
select 3 as eid,'ada'as fname,'sda' as lname,100 as points1,300 as points2 union all
select 4 as eid,'wqw'as fname,'aas' as lname,500 as points1,600 as points2 union all
select 5 as eid,'sga'as fname,'sds' as lname,600 as points1,null as points2 union all
select 6 as eid,'ttt'as fname,'eee' as lname,null as points1,700 as points2
Thanks
NLV.
September 22, 2011 at 2:44 pm
SELECT a.eid, a.fname, a.lname, SUM(CASE b.estatus WHEN 'good' THEN b.points END) points1,
SUM(CASE b.estatus WHEN 'verygood' THEN b.points END) points2
FROM #mytable1 a
LEFT JOIN #mytable2 b
ON a.eid = b.eid
AND b.estatus IN ('good', 'verygood')
GROUP BY a.eid, a.fname, a.lname
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply