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

    Unanswered: CROSSTAB ::works in access but not in MS SQL 2000

    I am trying to fill a table from 2 other tables in MS SQL 2000
    the structure ::

    Table 1 --> Info
    InfoID
    Name

    Table 2 --> Item
    InfoID
    Num
    Value

    TRANSFORM Max(Item.Value) AS MaxValue
    SELECT Info.Name
    FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
    WHERE Item.Num In (10,12,15,100)
    GROUP BY Info.Name
    PIVOT Item.Num

    in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
    but in MS SQL it doesnt work at all

    does someone knows how to translate it for MS SQL (the table structures are exactly the same)?


    thank you

  2. #2
    Join Date
    Jun 2005
    Posts
    115
    I have tried

    SELECT Info.Name
    ,(CASE WHEN Item.Num = 10 THEN Item.value END) AS A
    ,(CASE WHEN Item.Num = 12 THEN Item.value END) AS B
    ,(CASE WHEN Item.Num = 15 THEN Item.value END) AS C
    ,(CASE WHEN Item.Num = 100 THEN Item.value END) AS D
    FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID

    i must get one line and i get > 1000 lines !

    with GROUP BY Info.Name it doesn't work

    what is wrong ?

    thank you
    Last edited by quentin; 11-13-05 at 11:18.

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    Info

    InfoID | Name
    ...............
    1 | jean
    2| Pierre

    ----------------------------------------

    Item

    InfoID | Num | Value
    ....................
    1 | 400 | 1_400
    1 | 10 | 1_10
    1 | 15 | 1_15
    1 | 12 | 1_12
    1 | 100 | 1_100
    1 | 40 | 1_40
    2 | 400 | 2_400
    2 | 10 | 2_10
    2 | 15 | 2_15
    2 | 12 | 2_12
    2 | 100 | 2_100
    2 | 40 | 2_40

    I must get ::


    Name | A | B | C | D
    .....................
    Jean | 1_10 | 1_12 | 1_15 | 1_100
    Pierre | 2_10 | 2_12 | 2_15 | 2_100

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Dunno if you have gotten a response yet. Crosstab is a native Access function that is not available in SQL 2000. You can google ags crosstab or RAC or SQL for a 3rd party add-on to SQL.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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