Column to CSV

jercolson · June 4th, 2008, 06:46 PM

I ran into a query that seemed quite simple, but for some reason I cannot figure it out. It is essentially de-relationalizing a database column into a comma separated list, but it isn't as easy as just using coalesce.

There are three tables: one with customers, one with customer types, and a mapping table (1 customer to N types).

CUSTOMERS
------------------------
CUSTOMERID (varchar, PK)
NAME (varchar)

CUSTOMERS_TYPES_MAP
---------------------
CUSTOMERID (varchar)
CUSTOMERTYPE (varchar)

CUSTOMERTYPES
---------------------
CUSTOMERTYPE (varchar)

What I'm working toward is a query that retrieves a comma-separated list of CUSTOMERTYPES per CUSTOMERID. So the output would be something like this:

CUSTOMER CUSTOMERTYPES
--------------------------------------------------------
Customer A Vendor, Manufacturer
Customer B Vendor
Customer C Vendor, Manufacturer, Retailer

I was able to do this by creating a function (see below) that accepts a customer ID and returns a single comma separated list... However, I cannot use a function (long story... dumb customer). Any way to do this in a single statement without the use of a function?

CREATE FUNCTION getCustomerTypes(@CustomerId varchar(16))
RETURNS varchar(1024)
AS
BEGIN
      DECLARE @CustomerTypeList varchar(1024)
      SELECT @CustomerTypeList = COALESCE(@CustomerTypeList + ', ', '') +
                 CAST (CTM.CUSTOMERTYPE AS varchar(32))
      FROM CUSTOMERS_TYPES_MAP CTM
      WHERE CTM.CUSTOMERID = @CustomerId

      RETURN @CustomerTypeList
END

SELECT C.CUSTOMERID,
       C.NAME,
       dbo.getCustomerTypes(C.ID) CUSTOMERTYPES
FROM CUSTOMERS C

Jeff Moden · June 4th, 2008, 08:02 PM

Understood about the customer... everyone thinks conatenation functions are bad... have them take a look at the following article...

http://www.sqlservercentral.com/arti...st+Data/61572/

This will solve the problem for you, though... It's XML so it'll be just a touch slower...

Code:

DECLARE @tbl1 TABLE( ID INT, Descr VARCHAR(25))
DECLARE @tbl2 TABLE( ID INT, Descr VARCHAR(25))

 --==== Create a couple of test tables
 INSERT INTO @tbl1
 SELECT 1, 'test1' UNION ALL
 SELECT 2, 'test2' UNION ALL
 SELECT 3, 'test3'

INSERT INTO @tbl2
 SELECT 1, 'test1 additional'  UNION ALL
 SELECT 1, 'test1 additional2' UNION ALL
 SELECT 2, 'test2 additional'  

--===== Concatenatate the data without a function
 SELECT t1.ID , 
        STUFF((SELECT ',' + t2.Descr FROM @tbl2 t2 WHERE t1.id = t2.id FOR XML PATH('')),1,1,'')
   FROM @tbl2 t1
  GROUP BY t1.ID

--Jeff Moden