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

    Unanswered: Data in same row

    Hello guys,

    I have a table where data is in this format:

    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
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select T.ID, T.NAME,
          (CASE WHEN (select count(*) 
                     from table1 
                     where T.ID = table1.ID and
                              tabel1.OS = 'AIX') > 0 
               then 'Y'
               else 'N'
           END) as "AIX",
         .... repeat the above for each and every possible value of "OS" ...
    from (select distinct ID, NAME from table) as T
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2002
    Posts
    456
    My mistake in the initial post but the data is in MS Access 2000 database. I have posted in some MS Access forums too but nothing much have materialized.

  4. #4
    Join Date
    Sep 2002
    Posts
    456
    My mistake in the initial post but the data is in MS Access 2000 database. I have posted in some MS Access forums too but nothing much have materialized.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You will have to use IIF() instead of CASE.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2002
    Posts
    456
    Yes, I used the IIF statements and it worked. Thanks!

Posting Permissions

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