Post reply

insert three rows into one row of another table

  • February 22, 2003 at 11:50 am

    #151795

    Hi,

    I got stuck with following.

    I have a table with 3 rows:

    (Maximum number of dependants is 3)

    MemberSSN Dependant

    111111111 spouse

    111111111 child1

    111111111 child2

    How would I insert those 3 rows from one table into 1 row of another table(I cannot change the structure of that table) with following structure:

    MemberSSN Dependant1 Dependant2 Dependant3

    111111111 spouse child1 child2

    Thanks in advance,

    Andy

    ad

  • February 22, 2003 at 2:38 pm

    #449523

    Looks like you could do three left joins to your main table, something like:

    select mbrssn, d1.somevalue, d2.somevalue, d3.somevalue from maintable m

    left join dependents d1 on m.mbrssn = d1.mbrssn and dependent='spouse'

    left join dependents d2 on m.mbrssn = d1.mbrssn and dependent='child1'

    left join dependents d3 on m.mbrssn = d1.mbrssn and dependent='child2'

    I think that'll work as long as there 1 or 0 of each dependant type. If you end up with two spouses (bad idea anyway?) you'll get two rows returned from the join.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • February 24, 2003 at 12:48 pm

    #449624

    Here is an idea for you. It will output 1 or 0 depending on whether spouse or child exists or not. It is up to you, of course, to replace 1 with text. or 0 with blank.

    SELECTMemberSSN

    ,SUM( SIGN( CHARINDEX( 'spouse', Dependant ) ) )

    ,SUM( SIGN( CHARINDEX( 'child1', Dependant ) ) )

    ,SUM( SIGN( CHARINDEX( 'child2', Dependant ) ) )

    FROMtblDependants

    GROUP BY MemberSSN

  • February 24, 2003 at 4:59 pm

    #449660

    Thanks

    ad

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply