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 reference' ,@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
complex SQL (for me)
sneakers007
Ridi
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
N. Panoussis
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 histodsaddan
Crashin
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.
gung
Your sample is very nice but i don't know how to use it.
Lets me show what I want :
set
nocount oncreate 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
Eric C.
I cant use it becouse i need to use it as a navigation tool on web site
MartinHouse