Query for reordering tables

Hi folks!,
I have this table:
int char
+--+--+
| X1 | X2 |
+=====+=======+
| 1 | "A" |
+--+--+
| 1 | "B" |
+--+--+
| 2 | "C" |
+--+--+
| 2 | "D" |
+--+--+
I need a query which give me this (using that table):
(adding ";" between values)
int char
+--+--+
| z1 | z2 |
+=====+=========+
| 1 | "A;B" |
+--+--+
| 1 | "C;D" |
+--+--+
Any idea?
I have one using a cursor (fetch)... but after your suggests I will write you.
Thanks!
GMarquez
On Sun, 22 Apr 2007 08:20:03 -0700, GMarquez wrote:

>Hi folks!,
>I have this table:
> int char
>+--+--+
>| X1 | X2 |
>+=====+=======+
>| 1 | "A" |
>+--+--+
>| 1 | "B" |
>+--+--+
>| 2 | "C" |
>+--+--+
>| 2 | "D" |
>+--+--+
>I need a query which give me this (using that table):
>(adding ";" between values)
> int char
>+--+--+
>| z1 | z2 |
>+=====+=========+
>| 1 | "A;B" |
>+--+--+
>| 1 | "C;D" |
>+--+--+
>
>Any idea?
>I have one using a cursor (fetch)... but after your suggests I will write you.
>Thanks!
Hi GMarquez,
If you are on SQL Server 2005, you can use a variation on this trick:
USE AdventureWorks
GO
SELECT
CustomerID,
SalesOrderIDs = REPLACE(
(
SELECT
SalesOrderID AS [data()]
FROM
Sales.SalesOrderHeader soh
WHERE
soh.CustomerID = c.CustomerID
ORDER BY
SalesOrderID
FOR XML PATH ('')
), ' ', ';')
FROM
Sales.Customer c
ORDER BY
CustomerID;
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis