Flatten a recordset in SQL Server?

Question

Say you get a recordset like the following:

| ID  | Foo  | Bar  | Red  |
|-----|------|------|------|
| 1   | 100  | NULL | NULL |
| 1   | NULL | 200  | NULL |
| 1   | NULL | NULL | 300  |
| 2   | 400  | NULL | NULL |
| ... | ...  | ...  | ...  | -- etc.

And you want:

| ID  | Foo | Bar | Red |
|-----|-----|-----|-----|
| 1   | 100 | 200 | 300 |
| 2   | 400 | ... | ... |
| ... | ... | ... | ... | -- etc.

You could use something like:

SELECT
  ID,
  MAX(Foo) AS Foo,
  MAX(Bar) AS Bar,
  MAX(Red) AS Red
FROM foobarred
GROUP BY ID

---

Now, how might you accomplish similar when Foo, Bar, and Red are VARCHAR?

| ID  | Foo      | Bar     | Red     |
|-----|----------|---------|---------|
| 1   | 'Text1'  | NULL    | NULL    |
| 1   | NULL     | 'Text2' | NULL    |
| 1   | NULL     | NULL    | 'Text3' |
| 2   | 'Test4'  | NULL    | NULL    |
| ... | ...      | ...     | ...     | -- etc.

To:

| ID  | Foo      | Bar     | Red     |
|-----|----------|---------|---------|
| 1   | 'Text1'  | 'Text2' | 'Text3' |
| 2   | 'Text4'  | ...     | ...     |
| ... | ...      | ...     | ...     | -- etc.

---

Currently working primarily with SQL Server 2000; but have access to 2005 servers.

Answer 1

The query you had above works just fine for VARCHAR fields as it did for INT fields. The problem with your query though is that if you have two rows with the same ID, and both of those rows had something in the "Foo" column, then only the one with the highest value (both for INT and VARCHAR) will be displayed.

Answer 2

I don't have access to a SQL2K box at the minute but select max(column) will work on nvarchars in 2005. The only problem will be if you have multiple text values under each column for each id in your original table...

CREATE TABLE Flatten (
    id int not null,
    foo Nvarchar(10) null,
    bar Nvarchar(10) null,
    red Nvarchar(10) null)

INSERT INTO Flatten (ID, foo, bar, red) VALUES (1, 'Text1', null, null)
INSERT INTO Flatten (ID, foo, bar, red) VALUES (1, null, 'Text2', null)
INSERT INTO Flatten (ID, foo, bar, red) VALUES (1, null, null, 'Text3')
INSERT INTO Flatten (ID, foo, bar, red) VALUES (2, 'Text4', null, null)



SELECT 
    ID, 
    max(foo),
    max(bar),
    max(red)
FROM
Flatten
GROUP BY ID

returns

ID          Foo        Bar        Red
----------- ---------- ---------- ----------
1           Text1      Text2      Text3
2           Text4      NULL       NULL