| Author |
 Topic  |
|
|
sengmubashir
Starting Member
Saudi Arabia
4 Posts |
 Posted - 03/17/2013 : 07:05:17
|
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
1481 Posts |
Posted - 03/17/2013 : 10:53:12
|
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); |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/18/2013 : 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
Saudi Arabia
4 Posts |
Posted - 03/18/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/18/2013 : 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
Saudi Arabia
4 Posts |
Posted - 03/18/2013 : 11:32:07
|
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 |
|
|
|
sengmubashir
Starting Member
Saudi Arabia
4 Posts |
Posted - 03/18/2013 : 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
India
47023 Posts |
Posted - 03/18/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/19/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/19/2013 : 00:42:43
|
quote:
Originally posted by visakh16
Hi visakh,
i have already provided same solution for OP...
--
Chandu |
|
|
| |
Topic |
|
|
|
How to concatinate items having same Id using STUF
