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
NrLetters
1A
2A
2B
2C
3A
3B
3C
3D
3E
4A

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

OlivierOlivier (olivier.lammens@.belgacom.be) writes:
> 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

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/techin.../2000/books.asp|||olivier.lammens@.belgacom.be (Olivier) wrote in message news:<66db75c6.0412020207.4eb3a569@.posting.google.com>...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> NrLetters
> 1A
> 2A
> 2B
> 2C
> 3A
> 3B
> 3C
> 3D
> 3E
> 4A
> The result I want to get from an select:
> NrAll Letters
> 1A
> 2A, B, C
> 3A, B, C, D, E
> 4A
> Olivier

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|||"Olivier" <olivier.lammens@.belgacom.be> wrote in message news:66db75c6.0412020207.4eb3a569@.posting.google.c om...
> 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

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-|||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
--