Microsoft SQL Server forum - some sort of cross tab query in sql

Author

some sort of cross tab query in sql

NAJH

2005-05-26, 9:23 am

I have three tables:

tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode

tblBookRange has the fields bookRangeID, bookRangeDescription

tblBookSubject has the fields bookSubjectID, bookSubjectDescripti
on

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) )

Mike Zaremba

2005-05-26, 8:24 pm

The idea is:
create a temporary table, and then add columns to it inside a cursor;
then another cursor is used to calculate and update every cell

A bit lengthy, but works...
The main limitation of this approach is that it fails to work when your
row gets longer than 8000 bytes

I hope the code is readable

--MS SQL 2000

SET NOCOUNT ON

CREATE TABLE #T (bookRangeID int, BookRange nvarchar(50) not null
default(N''))

INSERT INTO #T(bookRangeID,BookR
ange)
SELECT bookRangeID,bookRang
eDescription
FROM tblBookRange

DECLARE @SQL nvarchar(4000)
DECLARE @bookSubjectID int,@bookSubjectDesc
ription nvarchar(50)

DECLARE subjects_cursor CURSOR FOR
SELECT bookSubjectID,bookSu
bjectDescription
FROM tblBookSubject

OPEN subjects_cursor

FETCH NEXT FROM subjects_cursor
INTO @bookSubjectID,@book
SubjectDescription

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL='ALTER TABLE #T ADD & #91;'+@bookSubjectDe
scription+']
nvarchar(200)'
EXEC (@SQL)

DECLARE @bookCode nvarchar(50),@bookRa
ngeID int
DECLARE @bookRangeIDCurrent int

DECLARE books_cursor CURSOR FOR
SELECT bookRangeID,bookCode

FROM tblBook
WHERE bookSubjectID=@bookS
ubjectID
ORDER BY bookRangeID

OPEN books_cursor

SET @SQL=''

FETCH NEXT FROM books_cursor
INTO @bookRangeID, @bookCode

WHILE @@FETCH_STATUS = 0
BEGIN
SET @bookRangeIDCurrent=
@bookRangeID

SET @SQL=@SQL+@bookCode+
' '

FETCH NEXT FROM books_cursor
INTO @bookRangeID, @bookCode

IF @@FETCH_STATUS <> 0 OR @bookRangeID<>@bookRangeIDCurrent
BEGIN
SET @SQL='UPDATE #T SET
& #91;'+@bookSubjectDe
scription+']='''+LEF
T(@SQL,LEN(@SQL)-1)+''' WHERE
bookRangeID='+conver
t(nvarchar,@bookRang
eIDCurrent)
EXEC(@SQL)
SET @SQL=''
END

END

CLOSE books_cursor
DEALLOCATE books_cursor

FETCH NEXT FROM subjects_cursor
INTO @bookSubjectID,@book
SubjectDescription
END

CLOSE subjects_cursor
DEALLOCATE subjects_cursor

SELECT * FROM #T

DROP TABLE #T

Erland Sommarskog

2005-05-26, 8:24 pm

NAJH (neilonusenet@yahoo.co.uk) writes:
> 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".)

I am not surprised that it got nasty and convoluted, because that is
precisely what the desired result set is from an SQL perspective.

I would defintely do this in a client-side language.

If I really had to do it in SQL, I would look at what RAC could help
me with. You find RAC at http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links