Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

558 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

558 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

558 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

558 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  
 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.12 seconds. Powered By: Snitz Forums 2000