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;

This page was last updated on May 01, 2006 04:28 PM.