Re: Query for reordering tables

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
.