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)
 How to concatinate items having same Id using STUF
 New Topic  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

3565 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
2202 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
2202 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
52309 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
2202 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
2202 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  
 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.08 seconds. Powered By: Snitz Forums 2000