query 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)

query help

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2009-06-30 : 12:43:36

Hello all,

can someboby help me how to get the following query

DATA EXAMPLE

col1 col2 orderdate
companya pizza 3/4/2009
companya donuts 6/4/2009
companya bagels 7/4/2009

companyb bagels 7/4/2009
companyb donuts 5/4/2009

OUTPUT SHOULD BE IN THIS FORMAT

COL1 PIZZA donuts bagels
Companya 3/4/2009 6/4/2009 7/4/2009

Companyb NULL 5/4/2009 7/4/2009

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:08:37

[code]
SELECT COL1,
MAX(CASE WHEN col2 ='Pizza' THEN orderdate ELSE NULL END) AS Pizza,
MAX(CASE WHEN col2 ='bagels' THEN orderdate ELSE NULL END) AS bagels,
MAX(CASE WHEN col2 ='donuts' THEN orderdate ELSE NULL END) AS donuts
FROM Table
GROUP BY COL1
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-01 : 05:16:39

try this too


SELECT col1,[pizza],[bagels],[donuts]
FROM @i
PIVOT (MAX(orderdate) FOR col2 IN ([pizza],[bagels],[donuts]))p

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-01 : 06:09:10

Hi try this too, using dynamic pivot


drop table #temp
create table  #temp  ( col1 varchar(32), col2 varchar(32), orderdate datetime )
insert into #temp select 'companya' ,'pizza'	,'3/4/2009'
insert into #temp select 'companya' ,'donuts' ,'6/4/2009'
insert into #temp select 'companya' ,'bagels' ,'7/4/2009'
insert into #temp select 'companyb' ,'bagels' ,'7/4/2009'
insert into #temp select 'companyb' ,'donuts' ,'5/4/2009'

DECLARE @strcols VARCHAR(MAX), @values VARCHAR(MAX),@string VARCHAR(MAX)

select @strcols = '' , @values = '' ,@string = '' 

SELECT @strcols =  @strcols + ', MAX([' + col2 + ']) '+ col2,
		@values = @values + ', ['+ col2 + ']'
from ( select distinct col2 from #temp ) t

select @string = 'select col1'+@strcols+'
		from #temp
		pivot(max(orderdate) for col2 in (' + stuff(@values,1,1,'') + '))p
		group by col1'
exec(@string)