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)
 How to concatinate items having same Id using STUF
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sengmubashir
Starting Member

Saudi Arabia
4 Posts

Posted - 03/17/2013 :  07:05:17  Show Profile  Reply with Quote
SQL SERVER 2008 R2, I have three tables as follows:

OrderProductVariant
Id | ProductVariantId
----------------------------------------
1 | 22
2 | 23
3 | 24
4 | 25

ProductVariant
Id | ProductId
----------------------------------------
22 | 34
22 | 35
23 | 36
23 | 37
24 | 38
24 | 39

Product
Id | Product
----------------------------------------
34 | KBDMouse800
35 | KBDMK250
36 | LaptopCorei7
37 | LaptopCorei5
38 | BluetoothMouse1000
39 | PresentorR800

I want the output result to be :

OrderProductVariant.Id | Product
-----------------------------------------
1 | KBDMouse800, KBDMK250
2 | LaptopCorei7, LaptopCorei5
3 | BluetoothMouse1000, PresentorR800

I have a limitation of not using Curser and Union

Mubashir Siddique
Developer

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 03/17/2013 :  10:53:12  Show Profile  Reply with Quote
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);
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/18/2013 :  02:56:13  Show Profile  Reply with Quote
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
Go to Top of Page

sengmubashir
Starting Member

Saudi Arabia
4 Posts

Posted - 03/18/2013 :  04:56:40  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/18/2013 :  07:09:18  Show Profile  Reply with Quote
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
Go to Top of Page

sengmubashir
Starting Member

Saudi Arabia
4 Posts

Posted - 03/18/2013 :  11:32:07  Show Profile  Reply with Quote
I am Really sorry, I got a mistake in problem statement, I have these actually as follows: (relationship is one step ahead more)

OrderProductVariant
Id | ProductVariantId
----------------------------------------
1 | 22
1 | 23
2 | 24
2 | 25
3 | 24
3 | 25

ProductVariant
Id | ProductId
----------------------------------------
22 | 34
23 | 35
24 | 36
25 | 37
26 | 38
27 | 39

Product
Id | Product
----------------------------------------
34 | KBDMouse800
35 | KBDMK250
36 | LaptopCorei7
37 | LaptopCorei5
38 | BluetoothMouse1000
39 | PresentorR800

I want the output result to be :

OrderProductVariant.Id | Product
-----------------------------------------
1 | KBDMouse800, KBDMK250
2 | LaptopCorei7, LaptopCorei5
3 | BluetoothMouse1000, PresentorR800



Mubashir Siddique
Developer
Go to Top of Page

sengmubashir
Starting Member

Saudi Arabia
4 Posts

Posted - 03/18/2013 :  13:54:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/18/2013 :  14:21:43  Show Profile  Reply with Quote
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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/19/2013 :  00:40:49  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/19/2013 :  00:42:43  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Hi visakh,

i have already provided same solution for OP...

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