to display data horiz without hardcoding values

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 2000 Forums

SQL Server Development (2000)

to display data horiz without hardcoding values

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-10-07 : 11:41:50

Hi
I have table with the following data. want to display horizontally without harcoding values in CASE expression.

create table #temp1
(
application varchar(100),
insertdate varchar(20),
quantity int)

insert into #temp1 values ('adobe 1.1','2009-09-02',34)
insert into #temp1 values ('adobe 1.2','2009-09-02',45)
insert into #temp1 values ('adobe 1.1','2009-09-09',100)
insert into #temp1 values ('adobe 1.2','2009-09-09',78)
insert into #temp1 values ('adobe 1.1','2009-09-16',900)
insert into #temp1 values ('adobe 1.2','2009-09-16',58)

OUTPUT SHOULD BE
-----------------------------------------------------------
APPLICATION 2009-09-02 2009-09-09 2009-09-16
------------------------------------------------------------
adobe 1.1 34 100 900
adobe 1.2 45 78 58
------------------------------------------------------------

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-10-07 : 12:25:56

I got this code and it works

can some one kinldy explain the how this code works

declare @case varchar(8000)
select @CASE = coalesce(@CASE + ' ,max(case when insertdate = ''' + insertdate + ''' then quantity else 0 end) as [' + insertdate + ']' + char(13)
, ' ,max(case when insertdate = ''' + insertdate + ''' then quantity else 0 end) as [' + insertdate + ']' + char(13))
from #temp1
group by insertdate

exec( 'select application' + @case + '
from #temp1
group by application')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 02:47:08

quote:
Originally posted by sachingovekar

I got this code and it works

can some one kinldy explain the how this code works

declare @case varchar(8000)
select @CASE = coalesce(@CASE + ' ,max(case when insertdate = ''' + insertdate + ''' then quantity else 0 end) as [' + insertdate + ']' + char(13)
, ' ,max(case when insertdate = ''' + insertdate + ''' then quantity else 0 end) as [' + insertdate + ']' + char(13))
from #temp1
group by insertdate

exec( 'select application' + @case + '
from #temp1
group by application')

It constructs CASE expression for all the values and then finally gets executed using exec

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources