problem --Self join concat- many to many

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

General SQL Server Forums

New to SQL Server Programming

problem --Self join concat- many to many

Author

Topic

Johnnybax1
Starting Member

5 Posts

Posted - 2011-12-08 : 20:59:19

Hi All,

I have 4 tables

Manufacturer Table sample data

Manufacturer ID 12
Manufacturer Asia

Product Table sample data

ProductID 100
ProductName Bulb
manufacturerid 12

Supply Table

SupplierID Suppliername ParentSupplierID
1 ROOT NULL
2 Hardware 1
3 Concrete 2
4 Appliances 2

SupplyProductRel(many to many)

SupplierID ProductID
2 100
3 100

I want something like a table with a join

ProductID ProductName SupplierPhase1 SupplierPhase2 manufacturername
100 BULB HARDWARE|COncrete HARDWARE|Appliances ASIA

NOTE: SuppierPhase above is a concat between the root/sub level from supply table.

A query or any other approach you guys suggest will be very helpful.Very urgent and important please!!!!

Do suggest any other approaches. It is in vb.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 01:55:55

[code]
;With CTE
AS
(
SELECT p.ProductID ,p.ProductName,s1.Suppliername , ROW_NUMBER() OVER (PARTITION BY s1.SupplierID ORDER BY s2.SupplierID ) AS Rn
FROM SupplyProductRel sp
INNER JOIN Product p
ON p.ProductID =sp.ProductID
INNER JOIN Supply s1
ON ss.SupplierID = sp.SupplierID
INNER JOIN Supply s2
ON s2.ParentSupplierID = s1.SupplierID
)

SELECT ProductID ,ProductName,Suppliername + '|'+ MAX(CASE WHEN Rn=1 THEN Suppliername ELSE NULL END) AS SupplierPhase1,
Suppliername + '|'+ MAX(CASE WHEN Rn=2 THEN Suppliername ELSE NULL END) AS SupplierPhase2
FROM CTE
GROUP BY ProductID ,ProductName,Suppliername
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources