Posted by M A Srinivas on 05/26/07 06:56

On May 25, 11:50 pm, kyle.fitzger...@gmail.com wrote:
> Ok here is the PageCategory, Documents, Pages, and XREF_DOC_PAGE
> tables
>
> I was setting it up so I could just query the XREF table and pass in
> the pageID to give me all the files for that page but as I said before
> not sure how to write that query to format it with the columns I want,
> Is there a better way to set this up in the database ?
>
> CREATE TABLE [dbo].[PageCategory] (
> [PageCategoryID]int IDENTITY(1, 1) NOT NULL,
> [PageCategory]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [LastUpdatedID]int NULL,
> [LastUpdateDate]datetime NULL,
> PRIMARY KEY CLUSTERED ([PageCategoryID])
> )
> ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Documents] (
> [DocID]int IDENTITY(1, 1) NOT NULL,
> [DocTypeID]int NULL,
> [Title]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [LastUpdateID]int NULL,
> [LastUpdateDate]datetime NULL,
> [Description]text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [DocName]text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> PRIMARY KEY CLUSTERED ([DocID])
> )
> ON [PRIMARY]
> TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Pages] (
> [PageID]int IDENTITY(1, 1) NOT NULL,
> [PageShortName]nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [PageName]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [PageTitle]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [PageType]nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> PRIMARY KEY CLUSTERED ([PageID])
> )
> ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[XREF_Doc_Page] (
> [XREF_Doc_Page_ID]int IDENTITY(1, 1) NOT NULL,
> [DocID]int NULL,
> [PageID]int NULL,
> [PageCategoryID]int NULL,
> [SortOrder]int NULL,
> [LastUpdateID]int NULL,
> [LastUpdateDate]datetime NULL,
> PRIMARY KEY CLUSTERED ([XREF_Doc_Page_ID])
> )
> ON [PRIMARY]
> GO


You need a dynamic cross tab query , If Pagecategory are finite and
constant , you can do some thing like this
Select b.docname,
MAX(case when PageCategoryID = 1 then c.pagecategory end ) as
Pagecategory01,
MAX(case when PageCategoryID = 2 then c.pagecategory end ) as
Pagecategory02,
MAX(case when PageCategoryID = 3 then c.pagecategory end ) as
Pagecategory03,
MAX(case when PageCategoryID = 4 then c.pagecategory end ) as
Pagecategory04
FROM
(select distinct docid,pagecategoryid from XREF_Doc_Page) a
inner join documents b where a.docid = b.docid
inner join pagecatefory c where a.pagecategoryid = c.pagecategoryid
group by b.docname
order by b.docname

[Back to original message]