Concatenating values from multiple records into one

[Mike]

I need help writing the following stored procedure. I have a table with
records like the following:

Field1 Field2 Field3
A 1 a
A 2 a
A 3 b
B 1 a

I need to insert the following records, derived from the above, into a
second table:

A 1, 2, 3 a, b
B 1 a

That is, I need to create a record for each value of Field1 that
concatenates the values of Field2 and Field3 for all records with the same
value of Field1. While it's not quite as elegant, it would also be OK for the
fields in Table2 to contain duplicate values, for example:

A 1,2,3 a, a, b
B 1 a

I greatly, greatly appreciate any suggestions on how to approach this.

TIA,
Mike S.

[Eric Isaacs]

Your best bet would be to do it in code, not in SQL. In that case,
you would need these two result sets from the db...

SELECT DISTINCT
Field1,
Field2
FROM
TableName
ORDER BY
Field1

SELECT DISTINCT
Field1,
Field3
FROM
TableName
ORDER BY
Field1

Then combine them in code to do whatever you need to do with them in
your application.

-Eric Isaacs

[Plamen Ratchev]

Here is one example:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
col1 CHAR(1),
col2 INT,
col3 CHAR(1));

INSERT INTO Foo VALUES (1, 'A', 1, 'a');
INSERT INTO Foo VALUES (2, 'A', 2, 'a');
INSERT INTO Foo VALUES (3, 'A', 3, 'b');
INSERT INTO Foo VALUES (4, 'B', 1, 'a');

SELECT DISTINCT col1,
STUFF(col2_list, 1, 1, '') AS col2_list,
STUFF(col3_list, 1, 1, '') AS col3_list
FROM Foo AS A
CROSS APPLY(SELECT DISTINCT ',' + CAST(col2 AS VARCHAR(10))
FROM Foo AS B
WHERE B.col1 = A.col1
FOR XML PATH('')) AS B(col2_list)
CROSS APPLY(SELECT DISTINCT ',' + col3
FROM Foo AS C
WHERE C.col1 = A.col1
FOR XML PATH('')) AS C(col3_list);

/*

col1 col2_list col3_list
---- ---------- ----------
A 1,2,3 a,b
B 1 a

*/

DROP TABLE Foo;

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

[--CELKO--]

>> That is, I need to create a record [sic: rows are not records] for each value of Field1[fields are not columns] that concatenates the values of Field2 and Field3 for all records with the same value of Field1. <<

Can explain why you are smarter than 30+ years of RM theory and Dr
Codd? Can you tell why 1NF should be violated? Did you even know
that means "First Normal Form", the foundation of RDBMS?

>> I greatly, greatly appreciate any suggestions on how to approach this. <<

Please, please read any book on RDBMS. Since you don't know anything
about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

I use when I am teaching procedural programmers into SQL