SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 concantenate x rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

524 Posts

Posted - 03/27/2014 :  03:33:49  Show Profile  Reply with Quote
Hi

I have the following Query that can produce x number of rows. I would like to concantenate these rows into one single string.


SELECT DISTINCT dbo.Providers.Name, 

CASE 
  WHEN dbo.Order.IsFetched = 1 THEN 'Yes' 
  WHEN dbo.Order.IsFetched = 0 THEN 'No' 
  WHEN dbo.Order.IsFetched IS NULL  THEN 'No' 
END AS Status

FROM            dbo.Order INNER JOIN
                         dbo.Products ON dbo.Order.NodeID = dbo.Products.NodeId INNER JOIN
                         dbo.Providers ON dbo.Products.ProviderID = dbo.Providers.ProviderID
WHERE        (dbo.Order.CartID = N'xxx')



The Query does now produce a result like this..

Namn Status
Provider 1 Yes
Provider 2 Yes

I would like them in one string like this...

Provider 1 Yes, Provider 2 Yes



Hopefully I can use this query as a subquery in another Query..

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 04/01/2014 :  15:21:01  Show Profile  Reply with Quote
You can use a STUFF operator to do this. Take a look at this article for more understanding of STUFF.. http://sqlsaga.com/sql-server/how-to-concatenate-rows-into-one-row-using-stuff/



Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 04/01/2014 :  15:25:42  Show Profile  Reply with Quote
see this...


DECLARE @Input TABLE
(Name VARCHAR(10), [Status] VARCHAR(3))

INSERT INTO @Input VALUES('Provider1', 'Yes'),('Provider2', 'No')

;WITH CTE AS
(SELECT Name+' '+[Status] AS col1 FROM @Input)
SELECT DISTINCT STUFF((SELECT ','+col1 FROM CTE s2 FOR XML PATH('')),1,1,'') AS Name 
FROM CTE S1



Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

magmo
Aged Yak Warrior

524 Posts

Posted - 04/03/2014 :  03:02:26  Show Profile  Reply with Quote
Works just perfect, Thank you very much!
Go to Top of Page

magmo
Aged Yak Warrior

524 Posts

Posted - 04/03/2014 :  03:28:12  Show Profile  Reply with Quote
I created a function that take a ordernumber as in parameter and return the new concantenated string which I can use like this..

SELECT * FROM FuncFetchProviderInformation('xx-xxx-x')

Is it possible to use this in a query like this...




SELECT DISTINCT TOP (10) dbo.tbl_OrderInfo.CartID
FROM            dbo.tbl_Order INNER JOIN
                         dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID



so that each "dbo.tbl_OrderInfo.CartID" value is passed to the function and I in the above Query return 2 columns, "CartID" and the concantenated value fomr the function?
Go to Top of Page

magmo
Aged Yak Warrior

524 Posts

Posted - 04/03/2014 :  04:07:44  Show Profile  Reply with Quote
Hi


Ok, I found a way to do it like this...


SELECT DISTINCT TOP (10) dbo.tbl_OrderInfo.CartID, m.ProviderSummary
FROM            dbo.tbl_Order INNER JOIN
                         dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
CROSS APPLY FuncFetchProviderInformation(dbo.tbl_OrderInfo.CartID) m



It works but the Query execute very slow, this Query take 10 seconds to run. Am I doing something wrong?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000