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

[kyle.fitzgerald]

I'm just know basic SQL but not enough to write any complex queries.
The problem I'm facing right now keeps me thinking to use a Cursor but
I've seen a lot of posts on here saying Cursors are bad so I'm hoping
there is a complex query that can give me the data I need.

I have about 6 pages in website where I need to display a datagrid of
information. There should be 5 columns, Filename, and then 4 Category
Titles (These category titles are stored in a table called
PageCategory). I have another table, XREF_Doc_Page that stores the
PageID, DocID (ID to know what file it is), and PageCategoryID. So I
can query this table with a pageID to see all the results that should
be on this page but I don't know how to format it the way I need my
datagrid?

In order to have the records from PageCategory be columns, is this a
crosstab query or something?

My only thoughts right now are to user a cursor to query Pagecategory
and build a temp table somehow with these as the columns?? (Not sure
how'd that would work yet).

So the datagrid would have the 5 columns like I said and then just
list all files associated with this page and put a checkmark under
whichever category it was assigned to (example below...)

Files PageCat1 PageCat2
PageCat3 PageCat4

abc.pdf X
xyz.pdf X
jkl.pdf
x

[Ed Murphy]

ky*************@gmail.com wrote:

I'm just know basic SQL but not enough to write any complex queries.
The problem I'm facing right now keeps me thinking to use a Cursor but
I've seen a lot of posts on here saying Cursors are bad so I'm hoping
there is a complex query that can give me the data I need.

First off, post CREATE TABLE statements for your tables. Often a
redesign of the data layout makes the query much simpler.

[kyle.fitzgerald]

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

[M A Srinivas]

On May 25, 11:50 pm, [e-mail] 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

[rshivaraman]

hi :

The following is a sample of how to change a cursor query into a non-
cursor query
Trust this helps
-RS

declare
@where varchar(10),
@when varchar(7),
@who varchar(5),
--@continue int,
@cp_id int,
@count int,
@loop_ctr int,
@loop_max int

--declare c1 cursor
--local
--for
insert into temp_www
([when],[where],who)
(
select distinct
[when],
[where],
who
from cicaprod_duplicates
)
set @loop_max = (select max(www_id) from temp_www)
set @loop_ctr = 1
--open c1
print 'Start loop...'
while(@loop_ctr <= @loop_max)
begin
select @when = [when], @where = [where, @who = who
from temp_www
where www_id = @loop_ctr

print @when+' '+@where+' '+@who

set @count = (select count(cp_id) from cicaprod_nodup
where [when] = @when and [where] = @where and who = @who)

print '@count = '+cast(@count as varchar(5))

set @loop_ctr = @loop_ctr + 1
End -- while

print 'End loop...'
--close c1
--deallocate c1