SELECT CASE WHEN problem

[anselme]

Hello !
For MS SQL 2000/2500

I am having a table ::

User
Ident int NOT NULL,
Numb int NOT NULL,
Info nvarchar(100) NOT NULL,

with this query ::

SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident

I get ::

Numb | Info | Ident

100 | dgwrg | 1
200 | dgeth yiuyi | 1
300 | Agt hgn ngtn | 1

100 | jttytujky | 2
200 | yjtut iuiu | 2
300 | dgd 2gggwekew | 2


what I need is something like::

SELECT
(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
WHERE Ident = Ident (for the same Ident)

to get ::

InfoA | InfoB | InfoC | Ident

dgwrg | dgeth yiuyi | Agt hgn ngtn | 1
sssd | hhh | dhhhh | 2
dddjjj oo |fdjjh | rr yuuu | 2
jttytujky | yjtut iuiu | dgd 2gggwekew | 2



Actually I am getting a NULL value everywhere
of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all

thanks a ton for helping

[MAK]

use tempdb
go
create table users (Numb int, Info varchar(20), Ident int)
insert into users select 100 ,'dgwrg', 1
insert into users select 200 ,'dgeth yiuyi',1
insert into users select 300 ,'Agt hgn ngtn', 1
insert into users select 100 ,'jttytujky', 2
insert into users select 200 ,'yjtut iuiu',2
insert into users select 300 ,'dgd 2gggwekew', 2
go
SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident
go
SELECT Ident,
MAX(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
MAX(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
MAX(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
group by Ident

[anselme]

Mak it doesnt work in that way

there is no reason to get the MAX, i need all

with Ident = 2
I must get 3 columns , but as many rows as necessary