Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Cool Unanswered: Simple Query problem (but not for me!)

    Two tables:

    ID COL_A1 COL_A2
    ------------------------
    IDA1 A11 A21
    IDA2 A21 A22

    and

    ID COL_B1 COL_B2
    ------------------------
    IDB1 B11 B21
    IDB2 B21 B22

    I need the query that gives me the following data set - combines the ID data for both tables in one column, and the other data in their respective columns in the combined data set e.g.

    ID COL_A1 COL_A2 COL_B1 COL_B2
    ---------------------------------------------
    IDA1 A11 A21
    IDA2 A21 A22
    IDB1 B11 B21
    IDB2 B21 B22

    (The B values should show under the B columns)

    Can anyone help?

    Thanks
    Greg

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTableA([ID] char(4), COL_A1 char(3), COL_A2 char(3))
    CREATE TABLE myTableB([ID] char(4), COL_B1 char(3), COL_B2 char(3))
    GO
    
    
    INSERT INTO myTableA([ID], COL_A1, COL_A2)
    SELECT 'IDA1', 'A11', 'A21' UNION ALL
    SELECT 'IDA2', 'A21', 'A22'
    
    INSERT INTO myTableB([ID], COL_B1, COL_B2)
    SELECT 'IDB1', 'B11', 'B21' UNION ALL
    SELECT 'IDB2', 'B21', 'B22'
    GO
    
    SELECT [ID], COL_A1, COL_A2, NULL AS COL_B1, NULL AS COL_B2
      FROM myTableA
    UNION ALL
    SELECT [ID], NULL AS COL_A1, NULL AS COL_A2, COL_B1, COL_B2
      FROM myTableB
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTableA
    DROP TABLE myTableB
    GO
    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
  •