one line for 3 rows

for MS SQL 2000
I am having :
Table1
ID | N1 | N2 | N3 |
------
12 | A | | |
12 | | B| |
12 | | | C|
how can I get :
12,A,B,C ?
thank you for helpingselect id, max(n1), max(n2), max(n3) from Table1 group by id|||Thank you roac|||Just to ask a quick question : why is your table laid out like that?
I'm just curious to know if this can be related to a real world example.|||we are getting datas in this way from another application and we cannot change it
I am taking the datas to put them in another table to read them normally for our application|||what happens when you get more than 1 value in any given column?|||In what format is this other application giving you data? Is the application writing directly to your database?

If you are handling the data before it gets entered into your database then I would seriously reconsider a redesign of the table format and of the way you are inserting data. There is no normalisation here, which is half the point of a database structure.

I don't see why the data cannot just be written into one row:
i.e.

ID| N1|N2|N3
------
12 | A | B | C

Do you have a more meaningful example of the data you are using? What datatypes are you utilising?

on delete cascade & hierarchical table

for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE

CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE

but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under

thank you for helpingI tried running your code on SQL Server 2005, and got the following message:

Introducing FOREIGN KEY constraint 'FK_Users_Sup' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In my opinion, this error message tells pretty well why you are not allowed to use on delete cascade on a self-join.|||You will need to implement this cascading referential integrity using a trigger.