SQL Server - How to concatenate multiple rows into single row, multiple columns

Asked By Marilyn

04-Aug-09 10:53 AM

Here is the task:
1.  A table has components
2.  Each component has layers
Sample:
COKEY        ..fields.. CHKEY | SOL_K1 | SOL_BD1 | SOL_...n
row1    1606010        xxxx   1425      xxxx       xxxx           xxxx
row2    1606010       xxxxx   1426     xxxxx      xxxx           xxxx
row3    1235873       xxxxx    1525    xxxx        xxxx           xxxx
row 4   ....etc.

I need to concatenate all CHKEYs that belong to the same COKEY, and RENUMBER
the columns, starting from CHKEY, by adding the numbers 1 to 10 (maximum
number of layers allowed).  So the final row should have
unique COKEYs.

COKEY  ....CHKEY | SOL_K1 | SOL_BD1 |...| SOLK2 | SOL_BD2 ...
row1  1606010 ----------------------------
row2  1235873

Am I making any sense?  If this can also be done in C#, I'd appreciate any
advise in that direction, too.

Thanks for any help.

04-Aug-09 10:58 AM

Here are a few methods for implementing this in T-SQL:
http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

04-Aug-09 11:14 AM

I tried an example but it is good only if you are converting one column with
multiple records; I get error when I include more columns.

Marilyn

04-Aug-09 11:36 AM

Try this example:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
cokey INT,
sol_k1 INT,
sol_bd1 INT);

INSERT INTO Foo VALUES(1, 1606010, 1425, 1);
INSERT INTO Foo VALUES(2, 1606010, 1426, 2);
INSERT INTO Foo VALUES(3, 1235873, 1525, 1);

SELECT DISTINCT
cokey,
STUFF((SELECT '|' + CAST(B.sol_k1 AS VARCHAR(10)) + '|' +
CAST(B.sol_bd1 AS VARCHAR(10))
FROM Foo AS B
WHERE B.cokey = A.cokey
FOR XML PATH('')), 1, 1, '') AS list
FROM Foo AS A;

/*

cokey       list
----------- --------------
1235873     1525|1
1606010     1425|1|1426|2

*/

DROP TABLE Foo;

--
Plamen Ratchev
http://www.SQLStudio.com

04-Aug-09 06:12 PM

Hi,

Thank you for this.  It worked!

I exported the list field as delimited text file and managed to read them
back into individual columns.

Thanks again.