locked
How to add additional rows to result set RRS feed

  • Question

  • I have a table like this:

    ObjId Date Value
    100 '20100401' 12
    200 '20100501' 45
    200 '20100401' 37
    300 '20100501' 75
    300 '20100401' 69
    400 '20100401' 87
    

     

    I have to add additional rows to result set for objId's where there is no data at '20100501'

    100 '20100501' null
    100 '20100401' 12
    200 '20100501' 45
    200 '20100401' 37
    300 '20100501' 75
    300 '20100401' 69
    400 '20100501' null
    400 '20100401' 87
    

     

    What is the best way to do this?

     

    Here is the T-SQL script for the initial table:

    declare @datesTable table (objId int, date smalldatetime, value int)
    insert @datesTable
    select 100, '20100401', 12
    union all
    select 200, '20100501', 45
    union all
    select 200, '20100401', 37
    union all
    select 300, '20100501', 75
    union all
    select 300, '20100401', 69
    union all
    select 400, '20100401', 87
    
    select * from @datesTable
    

     

    Wednesday, April 7, 2010 8:06 AM

Answers

  • Thank you all.

    Here is the final solution I'll use:

     

    ;WITH MissingObj AS
    
    (
    
        SELECT objId 
    
        FROM @datesTable  d1
    
        WHERE NOT EXISTS (SELECT objId FROM @datesTable d2 WHERE d2.objId = d1.objId AND d2.date = '20100501')
    
    )
    
    INSERT INTO @datesTable(objId, date, value)
    
        SELECT
    
            mo.objId, '20100501', NULL
    
        FROM    
    
            MissingObj mo
    
    
    

     

    • Marked as answer by S Bramante Thursday, April 8, 2010 12:59 PM
    Thursday, April 8, 2010 12:59 PM

All replies

  • select objID, date = '2010-05-01 00:00:00', NULL
    from @datesTable
    WHERE    ObjID NOT IN (SELECT OBJID FROM @datesTable WHERE date = '2010-05-01 00:00:00')

     

    Thanks,

     

    Please chick on marked as answered if this solve your pblm

    Wednesday, April 7, 2010 8:13 AM
  • Alternative way to do this

     

    SELECT    C.*
    FROM    @DatesTable a
            RIGHT OUTER JOIN
            (
                SELECT    *
                FROM    (
                            SELECT    DISTINCT ObjId
                            FROM    @DatesTable
                        )A
                        CROSS JOIN
                        (
                            SELECT    DISTINCT Date
                            FROM    @DatesTable
                        )B
            ) c
            on a.ObjID = C.ObjID
            AND A.Date = C.Date
    WHERE    A.ObjID IS NULL

     

    Wednesday, April 7, 2010 8:17 AM
  • Thank you all.

    Here is the final solution I'll use:

     

    ;WITH MissingObj AS
    
    (
    
        SELECT objId 
    
        FROM @datesTable  d1
    
        WHERE NOT EXISTS (SELECT objId FROM @datesTable d2 WHERE d2.objId = d1.objId AND d2.date = '20100501')
    
    )
    
    INSERT INTO @datesTable(objId, date, value)
    
        SELECT
    
            mo.objId, '20100501', NULL
    
        FROM    
    
            MissingObj mo
    
    
    

     

    • Marked as answer by S Bramante Thursday, April 8, 2010 12:59 PM
    Thursday, April 8, 2010 12:59 PM