|
Subject:
|
Column to CSV
|
|
Posted By:
|
jercolson
|
Post Date:
|
6/4/2008 6:46:38 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
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
6/4/2008 8:02:53 PM
|
Understood about the customer... everyone thinks conatenation functions are bad... have them take a look at the following article...
http://www.sqlservercentral.com/articles/Test Data/61572/
This will solve the problem for you, though... It's XML so it'll be just a touch slower...
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
|
|
