Binaryworld - How to concatenate values that are grouped by columns using SELECT sta ... [ SQL Server -> T-SQL ]

USE tempdb;
GO
CREATE TABLE Tbl (Company char(1), Contact varchar(30));
INSERT Tbl VALUES('A'      , 'Smith');
INSERT Tbl VALUES('A'      , 'Jones');
INSERT Tbl VALUES('B'      , 'Green');
INSERT Tbl VALUES('B'      , 'West');
INSERT Tbl VALUES('B'      , 'Johnson');
SELECT * FROM Tbl;
-- Sample Output:
/*
Company Contact
------- ------------------------------
A    Smith
A    Jones
B    Green
B    West
B    Johnson
*/
GO
-- Objective:
/*
    Get the contact names for each company in a single column as a
    concatenated string. Though these kind of operations are better
    handled in the front-end or the reporting tool, there are times
    when you may want to do this in the back-end itself. One obvious
    reason is to avoid pulling all the data to the client.
*/
-- Build a view that counts the number of contacts for each company first.
CREATE VIEW vwTbl AS
SELECT t1.company, t1.contact,
(SELECT COUNT(*) FROM Tbl t2
WHERE t1.Company = t2.Company And t2.Contact <= t1.Contact) AS Cnt
FROM Tbl t1
GROUP BY t1.company, t1.contact
GO
SELECT * FROM vwTbl;
-- Sample Output:
/*
company contact            Cnt
------- ------------------------------ -----------
A    Jones                  1
A    Smith                  2
B    Green                  1
B    Johnson                 2
B    West                   3
*/
GO
-- A simple crosstab query
SELECT t1.company,
COALESCE( MIN( CASE WHEN t1.cnt = 1 THEN t1.Contact END) , '' ) + '\n ' +
COALESCE( MIN( CASE WHEN t1.cnt = 2 THEN t1.Contact END) , '' ) + '\n ' +
COALESCE( MIN( CASE WHEN t1.cnt = 3 THEN t1.Contact END) , '' ) AS Contacts
FROM vwTbl t1
GROUP BY t1.company;
/*
company Contacts
------- ------------------------------------------------------------------------------------------------
A    Jones\n Smith\n
B    Green\n Johnson\n West
*/
-- Another way of writing the same but not so efficient
SELECT COALESCE(t1.Company, t2.Company, t3.Company) AS Company,
COALESCE(t1.Contact, '') + ' \n ' + COALESCE(t2.Contact, '') + ' \n ' +
COALESCE(t3.Contact, '') AS Contacts
FROM (SELECT * FROM vwTbl WHERE cnt = 1) t1
FULL JOIN (SELECT * FROM vwTbl WHERE cnt = 2) t2
ON t1.Company = t2.Company
FULL JOIN (SELECT * FROM vwTbl WHERE cnt = 3) t3
ON t2.Company = t3.Company;
GO
DROP VIEW vwTbl;
DROP TABLE vwTbl;