cross tab type query in sql

I have three tables:
tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
tblBookRange has the fields bookRangeID, bookRangeDescription
tblBookSubject has the fields bookSubjectID, bookSubjectDescription
so some typical data in tblBook might be:
1, 1, 1, B1HBSCI
2, 1, 2, B2HBFRE1
3, 1, 3, B3HBGER
4, 2, 1, B4PBSCI
5, 2, 2, B5PBFRE
6, 2, 3, B6PBGER
7, 3, 1, B7CDSCI
8, 3, 2, B8CDFRE
9, 3, 3, B9CDGER1
10, 3, 3, B10CDGER2
11, 1, 2, B11HBFRE2
tblBookRange would be:
1, HardBack
2, PaperBack
3, CD Rom
tblBookSubject would be:
1, Science
2, French
3, German
I'd like to create a query which will return me the subjects along the
top, the book range down the side, and the bookcodes in the cells, a
bit like this:
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
Does that make any sense? So basically I'd like to get some kind of
dynamic SQL working which will do this kind of thing. I don't want to
hard code the subjects in or the book ranges. I get the feeling that
dynamic SQL is the way forward with this and possibly using a cursor or
two too, but it got quite nasty and convoluted when I tried various
attempts to get it working. (one of the ways I tried included working
out each result in a dynamic script, but it ran out of characters as
there were too many "subjects".)
If anyone has any nice but quite dynamic solutions, I'd be delighted to
hear.
(and I know some of you have already told me you don't like tables
beginnig with tbl, but I'm not hear for a lecture on naming
conventions, I'm hear to learn and share ideas :o) )The RAC utility could do this for you quite easily (similiar in
concept to Access crosstab but much more powerful).
But then you wouldn't have the opportunity to write your
own dynamic code and maintain it:)
www.rac4sql.net|||The RAC utility is well worth the relatively inexpensive cost. I think it i
s
$60 per database or $300+ for a site license. If you are doing many crossta
b
queries, you will save this much in time in the first couple of days.
Another thing to note though, the not-yet-officially-released SQL Server 200
5
has a pivot function which will easliy perform crosstab queries.
Archer
"Pike" wrote:

> The RAC utility could do this for you quite easily (similiar in
> concept to Access crosstab but much more powerful).
> But then you wouldn't have the opportunity to write your
> own dynamic code and maintain it:)
> www.rac4sql.net
>
>|||If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL (or
a
report writer that uses dynamic SQL) and all the evil that goes with it or h
ave
your report writer create your crosstab type result. You could also use
something like Excel (or other report writer) to call to a stored proc to ge
t
the data you want and have Excel do the pivoting/crosstabbing.
Thomas
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL stateme
nt
and return the results)?
Thomas
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
> The RAC utility is well worth the relatively inexpensive cost. I think it
is
> $60 per database or $300+ for a site license. If you are doing many cross
tab
> queries, you will save this much in time in the first couple of days.
> Another thing to note though, the not-yet-officially-released SQL Server 2
005
> has a pivot function which will easliy perform crosstab queries.
> Archer
> "Pike" wrote:
>|||You could do something like this:
create table tblBook (
bookID int, bookRangeID int, bookSubjectID int, bookCode varchar(50))
insert into tblBook values(1, 1, 1, 'B1HBSCI')
insert into tblBook values(2, 1, 2, 'B2HBFRE1')
insert into tblBook values(3, 1, 3, 'B3HBGER')
insert into tblBook values(4, 2, 1, 'B4PBSCI')
insert into tblBook values(5, 2, 2, 'B5PBFRE')
insert into tblBook values(6, 2, 3, 'B6PBGER')
insert into tblBook values(7, 3, 1, 'B7CDSCI')
insert into tblBook values(8, 3, 2, 'B8CDFRE')
insert into tblBook values(9, 3, 3, 'B9CDGER1')
insert into tblBook values(10, 3, 3, 'B10CDGER2')
insert into tblBook values(11, 1, 2, 'B11HBFRE2')
create table tblBookRange (bookRangeID int, bookRangeDescription
varchar(50))
insert into tblBookRange values (1, 'HardBack')
insert into tblBookRange values (2, 'PaperBack')
insert into tblBookRange values (3, 'CD Rom')
create table tblBookSubject(bookSubjectID int, bookSubjectDescription
varchar(50))
insert into tblBookSubject values(1, 'Science')
insert into tblBookSubject values(2, 'French')
insert into tblBookSubject values(3, 'German')
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
declare variables
declare @.p char(1000)
declare @.i char(12)
declare @.cntm int
declare @.cntn int
declare @.m int
declare @.n int
set @.p = ''
select @.cntm=count(distinct bookRangeID) from tblBookRange
set @.m = 1
select @.cntn=count(distinct bookSubjectID) from tblBookSubject
set @.n = 1
Process until no more items
while @.m <= @.cntm
begin
while @.n <= @.cntn
begin
string together all items with a comma between
select @.i = bookRangeDescription, @.p = rtrim(@.p) + ' ' + bookCode
from tblBook a join tblbookRange b on a.bookRangeID = b.bookRangeID
where a.bookRangeID = @.m and a.bookSubjectID = @.n
set @.n = @.n + 1
set @.p = rtrim(@.p) + ','
end
print detail row
print @.i + ' ' + rtrim(substring(@.p,1,len(@.p)))
set @.n = 1
set @.m = @.m + 1
set @.p = ''
end
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Yes. Here is the sql to execute a RAC stored procedure. The tool that
comes with RAC is not very straightforward, but manipulating the SQL to get
what you want is.
Execute rac
@.transform = 'max(ltrim(rtrim([qyRac_hits].[x]))) as result))',
@.rows = '[qyRac_hits].[test_name] & [qyRac_hits].[Analyte]',
@.pvtcol = '[qyRac_hits].[ENVIRON_sample_id_mod]',
@.racheck = 'y',
@.from = '[qyRac_hits]',
@.where = 'ENVIRON_sample_id_mod like ~%emw1-%~
and collection_date < ~4/1/2005~',
@.grand_totals='n',
@.defaultexceptions='Funct & Totals'
Archer
"Thomas Coleman" wrote:

> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL state
ment
> and return the results)?
>
> Thomas
>
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
> news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
>
>|||Hello Thomas,
For what it's worth:
Most novice and even experienced sql programmers assume RAC
builds a classic dynamic SELECT statement consisting of gobs
of classic CASE statements as seen in the plethora of sql books
illustrating an sql crosstab.This is NOT what RAC does.Given all
the functionality/features available, this method would be next to
impossible
and even if it could be done it probably would bring the server to a hault.
The basic dynamic query built is a SELECT/GROUP BY query based
on the transform (aggregate(s)), row(s) and the column to be pivoted.
This is the building block from which xtabs,various ranking functionality
and all other basic questions/options start with.The default method that
RAC uses to build the actual result utilitized bcp.So anything you see
in a text book or on a site that shows how to develope a dynamic
crosstab in t-sql bears absolutely no resemblence to RAC:)
The real goal of RAC is to unburdon the user/developer of the messy
details of creating a myraid of dynamic results for various data
manipulation problems without stressing out the query optimizer.
Another example of *What* not *How*:)
Best from,
www.rac4sql.net
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL
> statement and return the results)?|||Understand, but quality of the code was not the reason I asked. My question
related to the permission issues with dynamic SQL. From what I understand, R
AC
would require providing direct table access to the users.
Thomas
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:uCv1hqkYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello Thomas,
> For what it's worth:
> Most novice and even experienced sql programmers assume RAC
> builds a classic dynamic SELECT statement consisting of gobs
> of classic CASE statements as seen in the plethora of sql books
> illustrating an sql crosstab.This is NOT what RAC does.Given all
> the functionality/features available, this method would be next to impossi
ble
> and even if it could be done it probably would bring the server to a hault
.
> The basic dynamic query built is a SELECT/GROUP BY query based
> on the transform (aggregate(s)), row(s) and the column to be pivoted.
> This is the building block from which xtabs,various ranking functionality
> and all other basic questions/options start with.The default method that
> RAC uses to build the actual result utilitized bcp.So anything you see
> in a text book or on a site that shows how to develope a dynamic
> crosstab in t-sql bears absolutely no resemblence to RAC:)
> The real goal of RAC is to unburdon the user/developer of the messy
> details of creating a myraid of dynamic results for various data
> manipulation problems without stressing out the query optimizer.
> Another example of *What* not *How*:)
> Best from,
> www.rac4sql.net
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23g5cFSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL
> (or a report writer that uses dynamic SQL) and all the evil that goes with
> it....
*Evil*...one would think that a professional might pick a more
appropriate term. But since its drummed into the head of every
user so often, it must be so:) Beware of piped pipers with long noses:)