complex SQL (for me)

Hello,

Lets look at this table :

CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Identifiant d''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Id'

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Date de l''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Week'

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Projet de rfrence' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Project'

It is a table where i store projects week reports.

I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.

I use SQL 2005. Thanks for any help

You can use a Matrix Report type that supported by many report products

like Crystal report ,

it is easy to use by a power full wizard

|||

I cant use it becouse i need to use it as a navigation tool on web site

|||

See if this set of posts helps you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

We did a rotation of a set like this (more generic in nature) in these posts.

|||

Your sample is very nice but i don't know how to use it.

Lets me show what I want :

set nocount on
create table Histo
(
Id varchar(10) primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-12','Internet'
union all
select '49','2006-13','Intranet'
go

select *
from Histo
go

it make this table :

Id | Week | Project
47 | 2006-12 | Internet
48 | 2006-12 | Internet
49 | 2006-13 | Intranet

And I Want to get something like that :

Project | 2006-12 | 2006-13
Internet | 47 | 49
Intranet | 48 | null

I work on it for days and dont find the solution.

Thanks a lot for any help

|||Well, this is the perfect example of the use of the PIVOT keyword in SQL Server 2005. If you're using SQL 2005 then use PIVOT. If you're using SQL 2000 then you'll have to use a case statement to accomplish this and it can be a bit cumbersome.|||Please demonstrate. I don't quite know how you will achieve what is being asked.|||nobody can help me ?|||

I found the solution :

set nocount on
create table Histo
(
Id varchar(10)primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-13','Internet'
union all
select '49','2006-12','Intranet'

go

select*
from Histo

go

SELECT Project,
[2006-12],
[2006-13]
FROM
(SELECT Project, week, id
FROM histo) s
PIVOT
(
max(id)
FOR Week IN ([2006-12],[2006-13])
) p
ORDER BY [Project]
go

drop table histo