How to concatinate items having same Id using STUF

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)

How to concatinate items having same Id using STUF

Author

Topic

sengmubashir
Starting Member

4 Posts

Posted - 2013-03-17 : 07:05:17

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-17 : 10:53:12

[code]SELECT
opv.ID,
STUFF(pn.Products,1,2,'') AS Products
FROM
OrderProductVariant opv
CROSS APPLY
(
SELECT
', '+p.Product
FROM
Product p
INNER JOIN ProductVariant pv
ON pv.ProductId = p.Id
WHERE
pv.Id = opv.ProductVariantId
FOR XML PATH('')
) pn(Products);[/code]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 02:56:13

Alternate without using CROSS APPLY clause
SELECT opv.Id, STUFF((SELECT ','+ p.Product FROM @Product p JOIN @ProductVariant pv ON p.Id = pv.ProductId WHERE opv.ProductVariantId = pv.Id FOR XML PATH('')), 1, 1, '') Products
FROM @OrderProductVariant opv;

--
Chandu

sengmubashir
Starting Member

4 Posts

Posted - 2013-03-18 : 04:56:40

Thanks for the reply guys but we need to have the result like I have shown in the end of problem which surely will need GROUP BY to concatenate strings having same Id. I am stuck at this point. It should show like opv.Id '3' has 'Row1string, Row2string'.

Result:
========
opv.Id | Product
-----------------------------------------
1 | KBDMouse800, KBDMK250
2 | LaptopCorei7, LaptopCorei5
3 | BluetoothMouse1000, PresentorR800

Mubashir Siddique
Developer

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 07:09:18

Have you executed earlier solutions?
Check the output for your sample data...
DECLARE @OrderProductVariant TABLE(Id INT, ProductVariantId INT)
INSERT INTO @OrderProductVariant VALUES(1, 22), (2, 23), (3, 24), (4, 25)

DECLARE @ProductVariant TABLE(Id INT, ProductId INT)
INSERT INTO @ProductVariant VALUES(22, 34), (22, 35), (23, 36), (23, 37), (24, 38), (24, 39)

DECLARE @Product TABLE(Id INT, Product VARCHAR(40))
INSERT INTO @Product
SELECT 34, 'KBDMouse800' UNION ALL
SELECT 35, 'KBDMK250' UNION ALL
SELECT 36, 'LaptopCorei7' UNION ALL
SELECT 37, 'LaptopCorei5' UNION ALL
SELECT 38, 'BluetoothMouse1000' UNION ALL
SELECT 39, 'PresentorR800'
/*I want the output result to be :
OrderProductVariant.Id | Product
-----------------------------------------
1 | KBDMouse800, KBDMK250
2 | LaptopCorei7, LaptopCorei5
3 | BluetoothMouse1000, PresentorR800*/

SELECT opv.Id, STUFF((SELECT ','+ p.Product FROM @Product p JOIN @ProductVariant pv ON p.Id = pv.ProductId WHERE opv.ProductVariantId = pv.Id FOR XML PATH('')), 1, 1, '') Products
FROM @OrderProductVariant opv;

Here we are checking for condition opv.ProductVariantId = pv.Id
So no need of GROUP BY clause

--
Chandu

sengmubashir
Starting Member

4 Posts

Posted - 2013-03-18 : 11:32:07

sengmubashir
Starting Member

4 Posts

Posted - 2013-03-18 : 13:54:03

Thanks all, What solution I am having right now is as follows:

SELECT [alfamel_sabic].[dbo].[OrderProductVariant].[OrderId] AS Id
,[alfamel_sabic].[dbo].[Product].[Name] AS Items
INTO #Temp FROM [alfamel_sabic].[dbo].[OrderProductVariant] LEFT JOIN [alfamel_sabic].[dbo].[ProductVariant] ON [alfamel_sabic].[dbo].[OrderProductVariant].[ProductVariantId]=[alfamel_sabic].[dbo].[ProductVariant].[Id]
LEFT JOIN [alfamel_sabic].[dbo].[Product] ON [alfamel_sabic].[dbo].[ProductVariant].[ProductId]=[alfamel_sabic].[dbo].[Product].[Id]
SELECT t.Id, STUFF((SELECT ', '+tmp.Items FROM #Temp tmp WHERE tmp.Id=t.Id FOR XML PATH('')), 1, 2, '') FROM #Temp t GROUP BY t.Id ORDER BY Id

But looking for a better logic without using temp.

Mubashir Siddique
Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-18 : 14:21:43

you dont need #Temp

just extend STUFF solution like this


SELECT o.Id,
STUFF((SELECT ',' + Product 
       FROM [Product] p 
       INNER JOIN ProductVariant pv
       ON pv.ProductId = p.Id
       WHERE pv.Id = o.ProductVariantId
       ORDER BY p.Id
       FOR XML PATH('')),1,1,'') AS Product
FROM OrderProductVariant o

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 00:40:49

whats the problem with the solution provided at (Posted - 03/18/2013 : 02:56:13 by bandi)?

Just change @tablenames with your fully qualified table names (i.e. along with servername.dbName.schemaName.TableName
For example,
@OrderProductVariant with OrderProductVariant
, @ProductVariant with ProductVariant
, and @Product with Product
)

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 00:42:43

quote:
Originally posted by visakh16

Hi visakh,

i have already provided same solution for OP...

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources