Click here to Skip to main content
14,690,541 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I am trying to convert rows into columns in SQL Server 2000. Although all rows converted into required columns but i unable to get the value converted into the respective columns.

My code is:

DROP TABLE prePivot1
DROP TABLE results1

CREATE TABLE prePivot1 (
    [StudentId] uniqueidentifier
    , [Sub_Abbr] Varchar(100)
    , [Total_marks] MONEY
    )

CREATE TABLE results1 (
    [StudentId] uniqueidentifier
    )

INSERT INTO prePivot1
SELECT       '{4FD7CBBA-1621-4102-B4A3-000BF92E2F6E}', 'ENG', 55
UNION SELECT '{4FD7CBBA-1621-4102-B4A3-000BF92E2F6E}', 'MBEN', 90
UNION SELECT '{4FD7CBBA-1621-4102-B4A3-000BF92E2F6E}', 'ECO', 80
UNION SELECT '{4FD7CBBA-1621-4102-B4A3-000BF92E2F6E}', 'PSc', 45
UNION SELECT '{4FD7CBBA-1621-4102-B4A3-000BF92E2F6E}', 'PSY', 23

UNION SELECT '{AD288712-5C97-446B-8AFE-003FC845B56B}', 'ENG', 90
UNION SELECT '{AD288712-5C97-446B-8AFE-003FC845B56B}', 'ECO', 44
UNION SELECT '{AD288712-5C97-446B-8AFE-003FC845B56B}', 'PSc', 45
UNION SELECT '{AD288712-5C97-446B-8AFE-003FC845B56B}', 'BST', 23
UNION SELECT '{AD288712-5C97-446B-8AFE-003FC845B56B}', 'ASS', 80
UNION SELECT '{AD288712-5C97-446B-8AFE-003FC845B56B}', 'PSY', 93




DECLARE @sql VARCHAR(8000)
DECLARE @pivot varchar(10)

DECLARE pivotCursor CURSOR LOCAL READ_ONLY FOR
SELECT DISTINCT
    [Sub_abbr]
FROM
    prePivot1

OPEN pivotCursor
    FETCH NEXT FROM pivotCursor INTO @pivot

    WHILE (@@FETCH_STATUS = 0) BEGIN

        SET @sql = '
        ALTER TABLE results1 ADD [' + CAST(@pivot AS VARCHAR(10))  + '] INT'
        EXEC (@sql)

        FETCH NEXT FROM pivotCursor INTO @pivot
    END
CLOSE pivotCursor

INSERT INTO results1 ([studentId])
SELECT DISTINCT [StudentId] FROM prePivot1

OPEN pivotCursor
    FETCH NEXT FROM pivotCursor INTO @pivot

    WHILE (@@FETCH_STATUS = 0) BEGIN

        SET @sql = '
        UPDATE results1
        SET
            [' + CAST(@pivot AS VARCHAR(10)) + '] = pp.[total_marks]
        FROM
            prePivot1 pp
        WHERE
            pp.[total_marks] = ' + CAST(@pivot AS VARCHAR(10)) + '
            AND pp.[studentId] = results1.[studentId]'

        EXEC (@sql)

        FETCH NEXT FROM pivotCursor INTO @pivot
    END
CLOSE pivotCursor
DEALLOCATE pivotCursor

SELECT * FROM results1




Please help me how do i proceed. Thanking you in advance.
Posted
Comments
Maciej Los 19-Dec-13 14:09pm
   
What you mean: "unable to get the value converted into the respective columns"?
apurba001 19-Dec-13 14:20pm
   
I get NULL in all sub_abbr columns.

1 solution

Try this:
SELECT DISTINCT StudentId, 
    (SELECT SUM(Total_marks) FROM prePivot1 AS t2 WHERE t2.StudentId = t1.StudentId AND Sub_Abbr = 'ENG') AS [ENG],
    (SELECT SUM(Total_marks) FROM prePivot1 AS t2 WHERE t2.StudentId = t1.StudentId AND Sub_Abbr = 'MBEN') AS [MBEN],
    (SELECT SUM(Total_marks) FROM prePivot1 AS t2 WHERE t2.StudentId = t1.StudentId AND Sub_Abbr = 'PSc') AS [PSc],
    (SELECT SUM(Total_marks) FROM prePivot1 AS t2 WHERE t2.StudentId = t1.StudentId AND Sub_Abbr = 'PSY') AS [PSY]
FROM prePivot1 AS t1


How to PIVOT Data Using T-SQL (SQL Server 2000, 2005 and 2008)[^]
   
Comments
CPallini 19-Dec-13 16:24pm
   
5.
Maciej Los 19-Dec-13 16:35pm
   
Thank you, Carlo ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900