Complicated Query - 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)

Complicated Query

Author

Topic

DougLowe
Starting Member

1 Post

Posted - 2009-06-16 : 20:00:52

Here's the situation:

LAYER TABLE  GROUP TABLE    COLORS TABLE
Name         Code           LayerName  GroupCode  Color
L1           BA             L1         BA         100          
L2           GD             L1         GD         130
L3           MA             L1         MA         120
                            L2         BA         90
                            L2         MA         105
                            L3         GD         115

Desired Result Table:

LayerName  BA    GD     MA
L1         100   130    120
L2         90    null   105 
L3         null  115    null

In other words, the result table will have one column for each row in the GROUP table, and the value of that column will be derived from the COLORS table.

Can this be done in SQL?[/font=Courier New]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 20:10:38

check out PIVOT in the BOL

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 21:11:15

[code]SELECT LayerName, BA, GD, MA
FROM
(SELECT c.*
FROM (Layer l right join Color c on l.name=c.layername)left join GroupTable g on c.groupcode=g.code)k
PIVOT
(SUM(color)FOR groupcode in(BA,GD,MA)) as pvt[/code]

ps. khtan y u so early wake up =.=

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 22:45:08

quote:
Originally posted by waterduck
ps. khtan y u so early wake up =.=

It's not that early. I normally wakes up much earlier

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 23:10:42

First answer that i provided!!! hehe

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 23:29:22

[code]
select [LayerName], [BA], [GD], [MA]
from
(
select l.[Name] as [LayerName],
c.[GroupCode],
c.[Color]
from LAYER l
left join COLORS c on l.[Name] = c.[LayerName]
) d
pivot
(
sum([Color])
for [GroupCode] in ([BA], [GD], [MA])
) p
[/code]

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 23:32:52

Argh do wrong...plz folo khtan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 23:50:42

quote:
Originally posted by waterduck

Argh do wrong...plz folo khtan

Not really, it still gives the same result that OP wanted. I just provide a different perspective view to the requirement.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-17 : 00:14:03

quote:

Originally posted by waterduck

SELECT	LayerName, BA, GD, MA
FROM
	(SELECT	c.*
	FROM	(Layer l right join Color c on l.name=c.layername) left join GroupTable g on c.groupcode=g.code)k
PIVOT
	(SUM(color)FOR groupcode in(BA,GD,MA)) as pvt

ps. khtan y u so early wake up =.=

Actually the LEFT JOIN to GroupTable is redundant there because it is not used at all in the SELECT statement.

Also for Layar RIGHT JOIN to the Color table, it will be better to be a LEFT JOIN. Unless you have LayerName appear in Color table but not in Layer table. In normal case, Color table should have a foreign key constraint to the Layer table. So it is possible that you have record in Layer table but not in Color table. A LEFT JOIN will make more sense.

If OP does not required that, then a select from Color table without joining to any other table will be able to produce the required result.

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-17 : 00:46:04

ic....coz wat i face alot nowadays is color have too many records that layer doesn...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-17 : 00:47:30

quote:
Originally posted by waterduck

ic....coz wat i face alot nowadays is color have too many records that layer doesn...

it will not be possible if you have foreign key constraint

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:13:12

[code]SELECT p.LayerName,
p.[BA],
p.[GD],
p.[MA]
FROM Colors AS c
PIVOT (
SUM(c.Color)
FOR c.GroupCode IN ([BA], [GD], [MA])
) AS p
ORDER BY p.LayerName[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources