Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: Data in the same row

    Hello guys,

    I have a table where data is in this format in Access table:

    ID Name OS
    ----------------------------------
    10 Paul AIX
    10 Paul SOLARIS
    10 Paul NT

    There is another column (4th) which is used for primary key, now the users want the data something like this:

    ID Name AIX SOLARIS NT OS390
    --------------------------------------------------------
    ID Paul Y Y Y N

    Is this possible and if yes, please share the SQL. Thanks.

    Paul

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    Look up "Pivot" in a good database book to get some backgropund on what is happening. Here's a first cut at a solution.

    select ID,
    Name,
    case
    when sum(case
    when OS = 'AIX' then 1 else 0 end) <> 0 then 'Y'
    else 'N'
    end,
    case
    when sum(case
    when OS = 'SOLARIS' then 1
    else 0
    end) <> 0 then 'Y'
    else 'N'
    end,
    case
    when sum(case
    when OS = 'NT' then 1
    else 0
    end) <> 0 then 'Y'
    else 'N'
    end,
    case
    when sum(case
    when OS = 'OS390' then 1
    else 0
    end) <> 0 then 'Y'
    else 'N'
    end
    group by ID, Name
    order by ID, Name
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Sep 2002
    Posts
    456
    The solution above did not work as I suppose Access 2000 does not allow CASE statement.

  4. #4
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    I'm a member of this form, just tyo ask the same question.
    In Access it is called a crosstab query.
    Go and make a new query with the wizzard, and try to make it work,.. it worked for me,..

    The way discribed above is a (T)SQL way and works if you use it directly on a SQL database.

    here a link to the topic i asked about it:
    http://www.dbforums.com/showthread.php?t=1211571

    and this link helped me to fullyundestand what to do (link uses sql, not ms access sql..)
    http://www.evolt.org/article/Using_M...token=53638642
    Last edited by MoonCrawler; 02-02-06 at 05:29.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dollar489
    The solution above did not work as I suppose Access 2000 does not allow CASE statement.
    yes, but you posted the question in the Microsoft SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So I'll move it for him.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a go through the crosstab query wizard, it will do the work for you. Not like pivot tables are hard to generate programmatically though...

    Code:
    TRANSFORM AggregateFunction
    SELECT row headers
    FROM yourTables
    GROUP BY row headers
    PIVOT Value;
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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