sql server (alternate):
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
[quoted text, click to view] kyle.fitzgerald@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
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
[quoted text, click to view] 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
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
Don't see what you're looking for? Try a search.
|
