By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,611 Members | 1,642 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,611 IT Pros & Developers. It's quick & easy.

SQL Server 2005: How to turn multiple rows into a single column

P: 2
How do I display multiple rows in a single column within the SQL Server 2005?

Sample Data

-----------------------------------------------------
Cust Name Cust_Order
------------------------------------------------------
ABCD ------ Calculator
ABCD ------ Radio
ABCD ------ TV
EFGH ------ Radio
IJKL ------ TV

Sample Result

-----------------------------------------------------
Cust Name Cust_Order
------------------------------------------------------
ABCD ------ Calculator, Radio, TV
EFGH ------ Radio
IJKL ------ TV
Dec 13 '07 #1
Share this Question
Share on Google+
2 Replies


jamesd0142
100+
P: 469
This can be modified to suit i think...

Expand|Select|Wrap|Line Numbers
  1. --results on 1 row........................
  2. DECLARE @a varchar(8000)
  3. SELECT @a=COALESCE(@a, '') + col01 + space(1) FROM (SELECT distinct (col01) from Dataset_Rows) as a
  4. SELECT rtrim(ltrim(@a)) AS [MyValues]
  5.  
Dec 13 '07 #2

Jim Doherty
Expert 100+
P: 897
This can be modified to suit i think...

Expand|Select|Wrap|Line Numbers
  1. --results on 1 row........................
  2. DECLARE @a varchar(8000)
  3. SELECT @a=COALESCE(@a, '') + col01 + space(1) FROM (SELECT distinct (col01) from Dataset_Rows) as a
  4. SELECT rtrim(ltrim(@a)) AS [MyValues]
  5.  

Hi James and eaocomp

The modification required to make this work for the poster centres around the fact that a concatenated list of values is required for each of the Customer names given. The code snippet you highlighted for amendment is not CustName centric (unless of course Dataset_Rows has some restrictive criteria based on the CustName) therefore it would return everything if you get me.

In short, for each customer the SQL is required to trundle down the products column as it were, and grab each unique mention of a product and fetch those values into 'one line' per customer separating out the products with a delimiter value which in this case is a required comma.

If the user creates a 'user defined function' to perform this functionality then this can be referenced in any views with the 'required' parameter being passed from the 'CustName' column value and the defined line of products return values then become visible in the view as a displayed column. We know that we can reference views in any stored procedures so we get the best of both worlds should they ever be required later.

This is the sample SQL that demonstrates the point of calling the UDF(User defined function) the udf function call being bolded

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DISTINCT [Cust Name], dbo.UDF_CustOrder([Cust Name]) AS Cust_Order
  3. FROM dbo.[Sample Data]
  4.  

The user defined function below to make this work is based on the table name 'Sample Data' as given obviously amend that to suit (hate spaces in object names but there you go) as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE FUNCTION dbo.UDF_CustOrder
  3. ( @custname varchar (50) )
  4. RETURNS varchar(8000)
  5. AS
  6. BEGIN
  7. DECLARE @Custorder varchar(255), @delimiter char
  8. SET @delimiter = ','
  9. SELECT @custorder = COALESCE(@custorder + @delimiter, '') +space(1)+ Cust_Order FROM (SELECT DISTINCT [Cust Name],Cust_Order FROM [Sample Data] where [Cust Name]=@CustName) c
  10. RETURN ( SELECT ltrim(rtrim(@CustOrder)) AS [CustOrder])
  11. END
  12.  

Hope this helps

Jim :)
Dec 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.