Select statement 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select statement help

Author

Topic

Esturk
Starting Member

1 Post

Posted - 2010-01-02 : 19:56:01

I got 2 tables:

Item / Desc
1 / One
2 / Two
3 / Three

&

Item / Month / Sold
1 / 5 / 10
1 / 6 / 11
2 / 5 / 20
2 / 6 / 21
3 / 5 / 30
3 / 6 / 31

Trying to figure out a select statement that will display the following:

Item / Desc / Month_5 / Month_6
1 / One / 10 / 11
2 / Two / 20 / 21
3 / Three / 30 / 31

Any help would be appreciated.

Esturk

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2010-01-02 : 22:48:38

select Item,[Desc],[5] as Month_5,[6] as Month_6 from
(
select I.Item,I.[Desc],ISo.[Month],ISo.[Sold] from Items I
inner join ItemSold ISo on I.Item = ISo.Item
)p
pivot (max([Sold]) for [MOnth] in ([5],[6]))pvt

and also please look at pivot in Books Online...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-02 : 22:57:56

[code]Declare @temp table
(item int,
[Desc] varchar(10))

Insert @temp
Select 1,'One' union all
Select 2,'Two' union all
Select 3,'Three'

Declare @tmp table
( item int,
[Month] int,
Sold int)

Insert @tmp
Select 1,5,10 union all
Select 1 ,6 ,11 union all
Select 2 , 5 , 20 union all
Select 2 , 6 ,21 union all
Select 3 , 5 , 30 union all
Select 3 , 6 , 31

Select p.item,[Desc],
Max(Case When P.Seq = 1 then Sold Else Null End) [Month_5],
Max(Case When P.Seq = 2 then Sold Else Null End) [Month_6]
--Max(Case ..Seq = 3 then ...End),....
from
(
Select tp.item,[Desc],Sold,ROW_Number() Over(Partition by tp.item,[Desc] Order by tp.item)as seq
from @temp tp
inner join @tmp tmp on tp.item = tmp.item
)P
Group by P.item,[Desc]
Order by P.item

Or Simply(If you are looking for Max and Min)

Select tp.item,[Desc],Min(Sold),Max(Sold)
from @temp tp
inner join @tmp tmp on tp.item = tmp.item[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-03 : 00:55:38

just small modification

quote:

Originally posted by sodeep

Declare @temp table
(item int,
 [Desc] varchar(10))

Insert @temp
Select 1,'One' union all
Select 2,'Two' union all
Select 3,'Three'

Declare @tmp table
( item int,
  [Month] int,
  Sold int)

Insert @tmp
Select 1,5,10 union all
Select 1 ,6 ,11 union all
Select 2 , 5 , 20 union all
Select 2 , 6 ,21 union all
Select 3 , 5 , 30 union all
Select 3 , 6 , 31 

Select p.item,[Desc],
Max(Case When P.Seq = 1 then Sold Else Null End) [Month_5],
Max(Case When P.Seq = 2 then Sold Else Null End) [Month_6]
--Max(Case ..Seq = 3 then ...End),....
from
(
Select tp.item,[Desc],Sold,ROW_Number() Over(Partition by tp.item,[Desc] Order by tp.item)as seq
from @temp tp
inner join @tmp tmp on tp.item = tmp.item
)P
Group by P.item,[Desc]
Order by P.item

Or Simply(If you are looking for Max and Min)

Select tp.item,[Desc],Min(Sold),Max(Sold)
from @temp tp
inner join @tmp tmp on tp.item = tmp.item

Group by P.item,[Desc]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources