Multiple values in one column

I'm trying to write a query which allows that multiple values from one
column are placed in one record.

ex:
table
Nr Letters
1 A
2 A
2 B
2 C
3 A
3 B
3 C
3 D
3 E
4 A

The result I want to get from an select:
Nr All Letters
1 A
2 A, B, C
3 A, B, C, D, E
4 A

Olivier

re: Multiple values in one column

Olivier (olivier.lammens@belgacom.be) writes:[color=blue]
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
>
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
>
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A[/color]

You cannot do this in a single query in SQL 2000. And this is not a
thing you are supposed to do in a relational database, because repeating
values in a column is not supposed to occur.

You can achieve it by an iterative solution and aggregat into a temp
table. (Some people may suggest shortcuts for this, but they are not
reliable.) But in essence, this is really something that should be performed
on the client side.

On a side note, this is actually possible to do in SQL2005, currently in
beta, by using some XML extensions. But it still not really a relational
thing to do, so the client is still the best place for the work.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Multiple values in one column

olivier.lammens@belgacom.be (Olivier) wrote in message news:<66db75c6.0412020207.4eb3a569@posting.google. com>...[color=blue]
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
>
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
>
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A
>
> Olivier[/color]

Hey, man!
Try to have a look at http://www.aspfaq.com/show.asp?id=2529
You have to write a UDF to concatenate first.
HTH

re: Multiple values in one column

"Olivier" <olivier.lammens@belgacom.be> wrote in message news:66db75c6.0412020207.4eb3a569@posting.google.c om...[color=blue]
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
>
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
>
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A
>
> Olivier[/color]

Just get a copy of iAnywhere's Adaptive Server Anywhere (ASA), which includes the List() aggregate function (and has had
it for about 8 years).

-Paul-

re: Multiple values in one column

The result you want isn't much use in a database - it's really a
formatted report for display or printing. For this reason it's probably
best done in your presentation tier rather than in SQL. If you have no
other option, however, you may be able to do something like this:

SELECT T.nr,
MAX(CASE WHEN seq=1 THEN letter END)+
MAX(CASE WHEN seq=2 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=3 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=4 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=5 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=6 THEN ','+letter ELSE '' END)
/* ... etc ... */
FROM
(SELECT T1.nr, T1.letter, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.nr = T2.nr
AND T1.letter >= T2.letter
GROUP BY T1.nr, T1.letter) AS T
GROUP BY T.nr

I'm assuming here that (nr,letter) is unique and non-NULL.
--
David Portas
SQL Server MVP
--