Data in same row

dollar489 · #1 (**permalink**) 02-01-06, 14:15

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

Wim · #2 (**permalink**) 02-01-06, 16:33

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

dollar489 · #3 (**permalink**) 02-02-06, 03:26

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.

dollar489 · #4 (**permalink**) 02-02-06, 03:26

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.

Wim · #5 (**permalink**) 02-02-06, 10:11

You will have to use IIF() instead of CASE.

dollar489 · #6 (**permalink**) 02-02-06, 10:16

Yes, I used the IIF statements and it worked. Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech