Re: transpose sql columns

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

.

Quantcast