Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: merging table columns?

    thanks for reading.

    kinda like like a UNION for columns maybe. i think that's what i'm looking for. take a look if you have a minute....

    3 queries, each pretty clear (count the occurrences of a value in a column --> where someFlag='Y')...

    SELECT Count(flag_A) as 'flag_A_confirmed', Year
    FROM cases
    WHERE flag_A = 'Y'
    GROUP BY Year


    SELECT Count(flag_B) as 'flag_B_confirmed', Year
    FROM cases
    WHERE flag_B = 'Y'
    GROUP BY Year


    SELECT Count(flag_C) as 'flag_C_confirmed', Year
    FROM cases
    WHERE flag_C = 'Y'
    GROUP BY Year



    but my desired output merges the columns together in one table ....

    total confirmed flags for that year by type...

    Year flag_A flag_B flag_C
    1998 12 15 33
    1999 16 13 25
    2000 19 14 21
    2001 ... etc


    i'm pretty sure i can do this using temp tables. any other ideas?

    thanks for your input in advance. again.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Join the tables to themselves


    SEKECT *
    FROM (DISTINCT Year) As Y
    INNER JOIN (SELECT ..FlagA='Y) AS A
    On y.year = b.year
    INNER JOIN (SELECT ..FlagB='Y) AS B
    On y.year = b.year


    get it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •