Sorry for the dumb question, but I am just new enough to sql that I
can't wrap my little brain around this one.

I have a system that I need to import data into and I need transpose a
few rows as columns. A good example would be if in pubs. I needed the
following results. Book, Author1_au_lname, Author1_au_fname,
Author2_au_lname, Author2_au_fname, Author3_au_lname,
Author3_au_fname. I need one row per book and if the book only has 1
author then the auth2 and auth3 columns are null. And if the book has
4 authors, then I lose the 4th authors data.

I am working on sql2000 and I would prefer a TSQL solution or maybe a
stored proc.

If this is a one time thing and performance is not an issue I would
use a cursor to "flatten" these records in either an t-sql script or a
stored proc...I can post an example if you need one..

-Jim

-->RUN THIS SCRIPT FOR EXAMPLE



-->VARIABLE DECLARATIONS
DECLARE @BOOK_ID INT,@AUTHOR_ID INT,@AUTHOR_COUNTER INT, @SQL
NVARCHAR(500)



-->TEST TABLES
CREATE TABLE #BOOK_TABLE
(
BOOK_ID INT IDENTITY(1,1),
BOOK_NAME VARCHAR(50)
)

CREATE TABLE #AUTHOR_BOOK_TABLE
(
BOOK_ID INT,
AUTHOR_ID INT
)


CREATE TABLE #AUTHOR_TABLE
(
AUTHOR_ID INT IDENTITY(1,1),
AUTHOR_FNAME VARCHAR(50),
AUTHOR_LNAME VARCHAR(50)
)


CREATE TABLE #BOOK_AUTHOR_IMPORT
(
BOOK_ID INT,
BOOK_NAME VARCHAR(50),
AUTHOR1_FNAME VARCHAR(50),
AUTHOR1_LNAME VARCHAR(50),
AUTHOR2_FNAME VARCHAR(50),
AUTHOR2_LNAME VARCHAR(50),
AUTHOR3_FNAME VARCHAR(50),
AUTHOR3_LNAME VARCHAR(50),
AUTHOR4_FNAME VARCHAR(50),
AUTHOR4_LNAME VARCHAR(50)
)

-->INSERTS DUMMY DATA
INSERT INTO #BOOK_TABLE(BOOK_NAME) VALUES ('BOOK_1')
INSERT INTO #BOOK_TABLE(BOOK_NAME) VALUES ('BOOK_2')

INSERT INTO #AUTHOR_TABLE(AUTHOR_FNAME, AUTHOR_LNAME) VALUES('ROGER',
'KLONDIKE')
INSERT INTO #AUTHOR_TABLE(AUTHOR_FNAME, AUTHOR_LNAME)
VALUES('CHARLIE', 'TANGO')
INSERT INTO #AUTHOR_TABLE(AUTHOR_FNAME, AUTHOR_LNAME) VALUES('ECHO',
'DELTA')
INSERT INTO #AUTHOR_TABLE(AUTHOR_FNAME, AUTHOR_LNAME) VALUES('ALPHA',
'BRAVO')

INSERT INTO #AUTHOR_BOOK_TABLE (BOOK_ID, AUTHOR_ID) VALUES (1, 1)
INSERT INTO #AUTHOR_BOOK_TABLE (BOOK_ID, AUTHOR_ID) VALUES (1, 2)
INSERT INTO #AUTHOR_BOOK_TABLE (BOOK_ID, AUTHOR_ID) VALUES (1, 3)
INSERT INTO #AUTHOR_BOOK_TABLE (BOOK_ID, AUTHOR_ID) VALUES (1, 4)
INSERT INTO #AUTHOR_BOOK_TABLE (BOOK_ID, AUTHOR_ID) VALUES (2, 3)
INSERT INTO #AUTHOR_BOOK_TABLE (BOOK_ID, AUTHOR_ID) VALUES (2, 1)



-->OUTTER CURSOR GETS EACH BOOK
DECLARE CURSOR1 CURSOR
FOR
SELECT
BOOK_ID
FROM
#BOOK_TABLE

OPEN CURSOR1

FETCH NEXT FROM CURSOR1 INTO @BOOK_ID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @AUTHOR_COUNTER = 1
INSERT INTO #BOOK_AUTHOR_IMPORT (BOOK_ID, BOOK_NAME)
SELECT BOOK_ID, BOOK_NAME FROM #BOOK_TABLE WHERE BOOK_ID = @BOOK_ID

-->INNER CURSOR GETS EACH AUTHOR FOR EACH BOOK
DECLARE CURSOR2 CURSOR
FOR
SELECT
A.AUTHOR_ID
FROM
#AUTHOR_TABLE A,
#AUTHOR_BOOK_TABLE B,
#BOOK_TABLE C
WHERE
C.BOOK_ID = @BOOK_ID
AND B.BOOK_ID = C.BOOK_ID
AND A.AUTHOR_ID = B.AUTHOR_ID

OPEN CURSOR2

FETCH NEXT FROM CURSOR2 INTO @AUTHOR_ID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='UPDATE A SET A.AUTHOR'+CAST(@AUTHOR_COUNTER AS VARCHAR)
+'_FNAME =B.AUTHOR_FNAME'+', A.AUTHOR'+CAST(@AUTHOR_COUNTER AS VARCHAR)
+'_LNAME =B.AUTHOR_LNAME'+' FROM #BOOK_AUTHOR_IMPORT A, #AUTHOR_TABLE
B '+' WHERE B.AUTHOR_ID ='+CAST(@AUTHOR_ID AS VARCHAR)+' AND
A.BOOK_ID='+CAST(@BOOK_ID AS VARCHAR)
EXEC(@SQL)

--PRINT @SQL
SET @AUTHOR_COUNTER = @AUTHOR_COUNTER + 1
FETCH NEXT FROM CURSOR2 INTO @AUTHOR_ID
END

CLOSE CURSOR2
DEALLOCATE CURSOR2


FETCH NEXT FROM CURSOR1 INTO @BOOK_ID
END

CLOSE CURSOR1
DEALLOCATE CURSOR1



-->NORMALIZED RESULTS
SELECT
C.BOOK_ID,
C.BOOK_NAME,
A.AUTHOR_FNAME,
A.AUTHOR_LNAME
FROM
#AUTHOR_TABLE A,
#AUTHOR_BOOK_TABLE B,
#BOOK_TABLE C
WHERE

B.BOOK_ID = C.BOOK_ID
AND A.AUTHOR_ID = B.AUTHOR_ID


-->DENORMALIZED RESULTS
SELECT
*
FROM
#BOOK_AUTHOR_IMPORT

-->CLEANS UP TEMP TABLES
DROP TABLE #AUTHOR_TABLE
DROP TABLE #AUTHOR_BOOK_TABLE
DROP TABLE #BOOK_TABLE
DROP TABLE #BOOK_AUTHOR_IMPORT

If you want only 3 authors and you want to drop the 4th you can add
"Top 3" to the sql statement for the second cursor

-Jim