i need help - 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

General SQL Server Forums

New to SQL Server Programming

i need help

Author

Topic

zez0
Starting Member

24 Posts

Posted - 2009-10-05 : 04:56:03

first Table
-----------------
Id Class Name
-----------------
1 Math
2 science
3 computer

second table
----------------------------
ClassIs Spesefications
----------------------------
3 123
1 147
2 369

I need query that give me this result

RESULT :

Math science computer
147 369 123

zezo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 04:57:49

are you using SQL 2005 / 2008 ?

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

zez0
Starting Member

24 Posts

Posted - 2009-10-05 : 05:01:21

SQL 2005

zezo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 05:03:10

[code]
declare @first Table
(
[Id] int,
[Class Name] varchar(10)
)
insert into @first
select 1, 'Math' union all
select 2, 'science' union all
select 3, 'computer'

declare @second table
(
[ClassIs] int,
[Spesefications] int
)
insert into @second
select 3, 123 union all
select 1, 147 union all
select 2, 369

-- SQL 2000 / 2005 / 2008
select Math = max(case when f.[Class Name] = 'Math' then [Spesefications] end),
science = max(case when f.[Class Name] = 'science' then [Spesefications] end),
computer = max(case when f.[Class Name] = 'computer' then [Spesefications] end)
from @first f
inner join @second s on f.Id = s.ClassIs

-- SQL 2005 / 2008
select *
from (
select f.[Class Name], s.[Spesefications]
from @first f
inner join @second s on f.Id = s.ClassIs
) d
pivot
(
max([Spesefications])
for [Class Name] in ([Math], [science], [computer])
) p
[/code]

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

zez0
Starting Member

24 Posts

Posted - 2009-10-05 : 05:20:55

Thanks

zezo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources