|
Execute the following
SQL Server 2008 T-SQL script in SSMS Query Editor to generate a book title - author junction table representing many-to-many relationship. XML Path is used to generate a list of authors for books with multiple authors.
-- T-SQL nested cursors demonstration
USE pubs;
SET nocount ON
DECLARE @BookTitle VARCHAR(16),
@AuthorID VARCHAR(32)
DECLARE @Result TABLE(
Title VARCHAR(128),
AuthorID VARCHAR(32)
)
-- Outer cursor
DECLARE curBookTitle CURSOR FOR
SELECT title_id
FROM titles
ORDER BY title_id
OPEN curBookTitle
FETCH curBookTitle
INTO @BookTitle
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'OUTER LOOP'
-- Inner nested cursor
DECLARE curTitleAuthor CURSOR FOR
SELECT au_id
FROM titleauthor
WHERE title_id = @BookTitle
OPEN curTitleAuthor
FETCH curTitleAuthor
INTO @AuthorID
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT ' INNER LOOP'
INSERT @Result
SELECT @BookTitle,
@AuthorID
FETCH curTitleAuthor
INTO @AuthorID
END
CLOSE curTitleAuthor
DEALLOCATE curTitleAuthor
FETCH curBookTitle
INTO @BookTitle
END
CLOSE curBookTitle
DEALLOCATE curBookTitle
-- Junction table simple listing
SELECT *
FROM @Result
ORDER BY Title,
AuthorID
/*
Title AuthorID
BU1032 213-46-8915
BU1032 409-56-7008
BU1111 267-41-2394
BU1111 724-80-9391
......
*/
-- Books with multiple authors listed on one line
-- XML path - text() will generate a comma separated list
SELECT DISTINCT Title,
AuthorList = STUFF((SELECT ', ' + AuthorID AS [text()]
FROM @Result r
WHERE r.Title = r1.Title
ORDER BY AuthorID
for XML Path ('')),1,1,'')
FROM @Result r1
GO
/*
Title AuthorList
BU1032 213-46-8915, 409-56-7008
BU1111 267-41-2394, 724-80-9391
BU2075 213-46-8915
BU7832 274-80-9391
MC2222 712-45-1867
MC3021 722-51-5454, 899-46-2035
.....
*/
------------ |
