在SQL Server 2000中将行转置为列 [英] Transpose Row to Column in SQL Server 2000

问题描述

我正在尝试将行转换为SQL Server 2000中的列。虽然所有行都转换为必需的列但我无法将值转换为相应的列。



我的代码是：

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.

推荐答案

试试这个：

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

如何PIVOT Da使用T-SQL（SQL Server 2000,2005和2008） [ ^ ]

这篇关于在SQL Server 2000中将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！