How can I get a pivot table with concatenated values?

Question

I have the following data:

|   ID     |   TYPE   |  USER_ID |
|----------|----------|----------|
|        1 |        A |        7 |
|        1 |        A |        8 |
|        1 |        B |        6 |
|        2 |        A |        9 |
|        2 |        B |        5 |

I'm trying to create a query to return

|   ID     |   RESULT |
|----------|----------|
|        1 |  7, 8, 6 |
|        2 |     9, 5 |

The USER_ID values must be ordered by the TYPE attribute.

Since I'm using MS ACCESS, I'm trying to pivot. What I've tried:

TRANSFORM first(user_id)
SELECT id, type
FROM mytable
GROUP BY id, type
ORDER BY type
PIVOT user_id

Error:

Too many crosstab column headers (4547).

I'm missing something in the syntax. However, it seems to be wrong since the first() aggregate needs to be changed to something else to concatenate the results.

PS: I'm using MS-ACCESS 2007. If you know a solution for SQL-Server or Oracle using only SQL (without vendor functions or stored procedures), I'll probably accept your answer since it will help me to find a solution for this problem.

Answer 1

You don't want to use PIVOT. Pivot will create a column named after each of your user IDs (1 - 7). Your TYPE field doesn't seem to do anything either.

Unfortunately, doing this in SQL Server requires the use of a function (FOR XML Path) that's not available in Access.

Here's a link with a similar Access function to do something similar.