Post reply

query to get data in one row by avoiding duplicates and should populate null in case of no value

  • September 22, 2011 at 10:16 am

    #248355

    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

    #1385456

    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