Re: Need Help with a SQL Statement - Trying not to use a Cursor

On May 25, 11:50 pm, kyle.fitzger...@xxxxxxxxx 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


.