Changing layout from rows to columns

Hi,

I am having problems to convert this layout in the way I want.

I have this table:.

Date Code Value
2002 01 101
2002 02 201
2002 03 301
2003 01 102
2003 04 202
2003 05 302
2004 02 103
2004 03 203

And this is the way I would like to receive the report:

Code Value 2002 Value 2003
Value 2004
01 101 102
0
02 201 0
103
03 301 0
203
04 0 202
0
05 0 302
0

The first problem is that some codes does not exist every year, so I have to
put 0 value on it. Second, the number of years can change. Thanks,
--

Mauricio Harger - Audit Brasil Consultores
mauricio RemoveThis @auditbrasil.com.br
+55 47 433-0222

hi Mauricio,

on the basis of below sample data see 2 solutions given below.

--sample table and data

create table t
(Date varchar (4),
Code varchar (2),
Value int )
go
insert into t values(2005,'01','101')
insert into t values(2005,'02','201')
insert into t values(2005,'03','301')
insert into t values(2006,'01','102')
insert into t values(2006,'04','202')
insert into t values(2006,'05','302')
insert into t values(2007,'02','103')
insert into t values(2007,'03','203')
go

--soln. 1)

if number of years are fixed and known to you. then you can have query as
follow.
(this hard codes the value.)

select distinct code ,
isnull((select value from t a where a.code = t.code and date = '2002'),0)
'2002',
isnull((select value from t a where a.code = t.code and date = '2003'),0)
'2003',
isnull((select value from t a where a.code = t.code and date = '2004'),0)
'2004'
from t

--soln. 2)

/*
if number of years are dynamic then you will have to make use of dynamic sql
and some procedural code
ex: using dynamic sql
*/

declare @dt int
declare @str varchar(8000)
select @dt = 0,@str=''

while 1=1
begin
select @dt=min(date) from t
where date > @dt

if (select min(date) from t where date > @dt) is null
break

set @str = @str + 'isnull((select value from t a where a.code = t.code and
date = ''' + cast(@dt as varchar(4)) + '''),0) ''' + cast(@dt as varchar(4))
+ ''','
end

set @str = @str + 'isnull((select value from t a where a.code = t.code and
date = ''' + cast(@dt as varchar(4)) + '''),0) ''' + cast(@dt as varchar(4))
+ ''''
set @str = 'select distinct code ,' + @str + ' from t'
exec (@str)

--
Vishal Parkar
vgparkar RemoveThis @yahoo.co.in | vgparkar RemoveThis @hotmail.com