Flaten join - SQL Server Forums

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)

Flaten join

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-24 : 04:15:56

Hello

I have Two table

T1=lnkLaptopComponent
T2=LaptopComponent

T1 = ModelID,ComponentType,ComponentID
T2 = PK,Type,Name

I have 4 types on component 'CPU,'HDD','RAM','OS'

So each ModelID in T1 will have 4 records related to T2

I need to set a view flattening the result of the join to get fo each ModelID 4 columns with Types and 4 columns with Names

Below is my query to get the 4 names colums
It works but is it the best way ?

Thank for your suggestions

SELECT     llc2.ModelID,
                          (SELECT     p2.Name + '' AS Expr1
                            FROM          dbo.LaptopComponents AS p2 INNER JOIN
                                                   dbo.lnkLaptopComponent AS llc ON llc.ComponentID = p2.PK
                            WHERE      (llc.ModelID = llc2.ModelID) AND (llc.ComponentType = 'hdd')) AS hdd,
                          (SELECT     p2.Name + '' AS Expr1
                            FROM          dbo.LaptopComponents AS p2 INNER JOIN
                                                   dbo.lnkLaptopComponent AS llc ON llc.ComponentID = p2.PK
                            WHERE      (llc.ModelID = llc2.ModelID) AND (llc.ComponentType = 'os')) AS os,
                          (SELECT     p2.Name + '' AS Expr1
                            FROM          dbo.LaptopComponents AS p2 INNER JOIN
                                                   dbo.lnkLaptopComponent AS llc ON llc.ComponentID = p2.PK
                            WHERE      (llc.ModelID = llc2.ModelID) AND (llc.ComponentType = 'ram')) AS ram,
                          (SELECT     p2.Name + '' AS Expr1
                            FROM          dbo.LaptopComponents AS p2 INNER JOIN
                                                   dbo.lnkLaptopComponent AS llc ON llc.ComponentID = p2.PK
                            WHERE      (llc.ModelID = llc2.ModelID) AND (llc.ComponentType = 'cpu')) AS cpu
FROM         dbo.LaptopComponents AS p1 INNER JOIN
                      dbo.lnkLaptopComponent AS llc2 ON llc2.ComponentID = p1.PK
GROUP BY llc2.ModelID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-24 : 04:55:20

try


SELECT     	llc.ModelID,
		max(case when llc.ComponentType = 'hdd' then lc.[Name] end) as hdd.
		max(case when llc.ComponentType = 'os' then lc.[Name] end) as os,
		max(case when llc.ComponentType = 'ram' then lc.[Name] end) as ram,
		max(case when llc.ComponentType = 'cpu' then lc.[Name] end) as cpu
FROM       	dbo.LaptopComponents 	AS lc
INNER JOIN	dbo.lnkLaptopComponent 	AS llc 	ON 	llc.ComponentID = lc.PK
WHERE		llc.ComponentType in ('hdd', 'os', 'ram', 'cpu')
GROUP BY 	llc.ModelID

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-24 : 04:57:08

or using PIVOT operator


select	*
from	(
		SELECT     	llc.ModelID,
				llc.ComponentType,
				lc.[Name]
		FROM       	dbo.LaptopComponents 	AS lc
		INNER JOIN	dbo.lnkLaptopComponent 	AS llc 	ON 	llc.ComponentID = lc.PK
		WHERE		llc.ComponentType in ('hdd', 'os', 'ram', 'cpu')
	) c
	pivot
	(
		max([Name])
		for ComponentType in ([hdd], [os], [ram], [cpu])
	) p

KH
[spoiler]Time is always against us[/spoiler]

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-24 : 07:13:40

thank you khtan

Your second proposition is great
Now I'm also trying to get the component ID id the result

To have

ModelID,ComponentID,ComponentName,ComponentId,ComponentName etc..

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-24 : 07:43:20

For now I'm using this

SELECT     	llc.ModelID,
		max(case when llc.ComponentType = 'hdd' then lc.[Name] end) as hdd,
		max(case when llc.ComponentType = 'hdd' then lc.[PK] end) as hddID,		
		max(case when llc.ComponentType = 'os' then lc.[Name] end) as os,
		max(case when llc.ComponentType = 'os' then lc.[PK] end) as osID,		
		max(case when llc.ComponentType = 'ram' then lc.[Name] end) as ram,
		max(case when llc.ComponentType = 'ram' then lc.[PK] end) as ramID,		
		max(case when llc.ComponentType = 'cpu' then lc.[Name] end) as cpu,
		max(case when llc.ComponentType = 'cpu' then lc.[PK] end) as cpuID		
FROM       	dbo.LaptopComponents 	AS lc
INNER JOIN	dbo.lnkLaptopComponent 	AS llc 	ON 	llc.ComponentID = lc.PK
WHERE		llc.ComponentType in ('hdd', 'os', 'ram', 'cpu')
GROUP BY 	llc.ModelID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-24 : 09:47:23

quote:
Originally posted by olibara

thank you khtan

Your second proposition is great
Now I'm also trying to get the component ID id the result

To have

ModelID,ComponentID,ComponentName,ComponentId,ComponentName etc..

for this, it is easier to use the max - case method as what you have posted

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources