Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: importing partials datas from 2 tables in another one

    I am having a problem to get all datas into one table
    I must get datas from 2 other tables
    datas from the first table are columns
    but datas of the second table are rows

    I cannot change the structure of TableA because an other application is using it as it is

    TableA
    num | value | key
    1 | a | 1400
    2 | b | 1401
    3 | c | 1402
    4 | d | 1403
    ...|... | 1403

    TableB
    key | name | descr | value
    400 | john | ok | 451

    TableC (the table where I want to insert datas from Table1 and Table2)

    val1 | val2 | val3 | val4 |name | descr | value
    1 | 2 | 3 |4 | john | ok | 451


    TableA has thousands of rows and different numbers
    I must get 8 different datas from TableA

    so I get an horrible Query !

    SELECT

    TableB.name, TableB.descr, TableB.value,
    TableA_1.value AS val1,
    TableA_2.value AS val2,
    TableA_3.value AS val3,
    TableA_4.value AS val4,
    TableA_5.value AS val5,
    TableA_6.value AS val6,
    TableA_7.value AS val7,
    TableA_8.value AS val8

    FROM dbo.TableB INNER JOIN
    dbo.TableA_1 ON TableB.key = TableA_1.key INNER JOIN
    dbo.TableA_2 ON TableB.key = TableA_2.key INNER JOIN
    dbo.TableA_3 ON TableB.key = TableA_3.key INNER JOIN
    dbo.TableA_4 ON TableB.key = TableA_4.key INNER JOIN
    dbo.TableA_5 ON TableB.key = TableA_5.key INNER JOIN
    dbo.TableA_6 ON TableB.key = TableA_6.key INNER JOIN
    dbo.TableA_7 ON TableB.key = TableA_7.key INNER JOIN
    dbo.TableA_8 ON TableB.key = TableA_8.key

    WHERE TableA_1.num = 145
    AND TableA_2.num = 80
    AND TableA_3.num = 3160
    AND TableA_4.num = 41
    AND TableA_5.num = 50
    AND TableA_6.num = 51
    AND TableA_7.num = 53
    AND TableA_8.num = 56


    how can i do it in the best way ?

    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a CROSSTAB query and you can do this with a single call to your table. Lookup CROSSTAB in Books Online and you will see an excellent example of how to do this using CASE statements.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    I had a look on line and i cannot find any exemple of what i need

    I wan that 2 lines from one table and 1 column from another one
    become 3 colomns in a third one


    if anyone has a link it will be great ?
    Last edited by quentin; 11-13-05 at 02:17.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    CROSSTAB in Books Online (installed with SQL Server).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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