Transpose records from 2 "one to many" tables into one record

Question

Basically, I would like to get a recordset similar to this:

CustomerID, CustomerName, OrderNumbers
1           John Smith    112, 113, 114, 115
2           James Smith   116, 117, 118

Currently I am using an Sql Server UDF to concatenate order #s.

Are there more efficient solutions ?

You shouldn't do that. Keep your two tables. It will cause you headaches instead later on. Like that you're violating the first normal form. http://en.wikipedia.org/wiki/First_normal_form — brunch875, Dec 31 '13 at 19:17
@brunch875 I don't think he wants to physically combine the two tables, I think he just wants a query to display like above, probably for reporting reasons. — Saggio, Dec 31 '13 at 19:18
Yes, just display for reporting reasons. I am now thinking about combining recordsets on the application level as opposed to the DB. — David, Dec 31 '13 at 19:20
@David I would try doing something similar as the accepted answer to this question: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Saggio, Dec 31 '13 at 19:34

score 0 · Answer 1 · answered Jan 01 '14 at 11:37

1) There are allot of solutions.

2) Example (SQL2005+):

DECLARE @Customer TABLE (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL
);
INSERT @Customer VALUES (1, 'Microsoft');
INSERT @Customer VALUES (2, 'Macrosoft');
INSERT @Customer VALUES (3, 'Appl3');

DECLARE @Order TABLE (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL -- FK
);
INSERT @Order VALUES (1, 1);
INSERT @Order VALUES (2, 2);
INSERT @Order VALUES (3, 2);
INSERT @Order VALUES (4, 1);
INSERT @Order VALUES (5, 2);

SELECT  c.CustomerID, c.Name, oa.OrderNumbers
FROM    @Customer AS c
/*CROSS or */OUTER APPLY (
    SELECT STUFF((
        SELECT ',' + CONVERT(VARCHAR(11), o.OrderID) FROM @Order AS o
        WHERE o.CustomerID = c.CustomerID
        -- ORDER BY o.OrderID -- Uncomment of order nums must be sorted
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS OrderNumbers
) oa;

Output:

CustomerID  Name      OrderNumbers
----------- --------- ------------
1           Microsoft 1,4
2           Macrosoft 2,3,5
3           Appl3     NULL

Transpose records from 2 "one to many" tables into one record

1 Answers1