concantenate x rows - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

concantenate x rows

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-03-27 : 03:33:49

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

93 Posts

Posted - 2014-04-01 : 15:21:01

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.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-01 : 15:25:42

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.

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-04-03 : 03:02:26

Works just perfect, Thank you very much!

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-04-03 : 03:28:12

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?

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-04-03 : 04:07:44

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources