我有这样的原始数据：

CodeRef ValueText ValueDate ValuedId  columname
99        red                           colour   
99                 20120404             apredate
99        new data                      comments
99                              999     code
98        black                         colour   
98                 20120413             apredate
98        old data                      comments
98                              888     code

我需要这样的结果。列名的上下文不是静态的。
我需要为valuetext、valuedate和valueid创建“columname”的柱基和pivot 3 time

CodeRef   Colour   apredate     comments      code
99          red     20120404    new data       999
98          black   20120413    old data       888

此存储过程的问题是创建三个表，但数据类型是depend valuetext valuedate valuedid

CREATE PROCEDURE [dbo].[sp_test]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @value AS NVARCHAR(MAX)
    DECLARE @ValueContent  AS NVARCHAR(MAX);
    DECLARE @RunningTotal BIGINT = 0
    DECLARE @sql AS NVARCHAR(MAX)
    DECLARE @sqlat AS NVARCHAR(MAX)

    -- table with all columns
    SELECT  @cols = 
                    STUFF((SELECT distinct ',' + QUOTENAME(columnname) 
                    FROM (
                            select distinct ProductReferenceID, columnname
                            FROM dbo.table
                            where ProductReferenceID = 172958
                            and ColumnName <> 'ProductReferenceID'
                    ) A 
                    FOR XML PATH ('') , TYPE) .value ( '.' , 'NVARCHAR(MAX)') ,1,1,'') 
    SELECT  @cols;
     -- table with only three column
                    DECLARE CUR_VALUE CURSOR FOR
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = 'table'
                    and COLUMN_NAME in ('ValueDate','ValueID','ValueText')

OPEN CUR_VALUE
FETCH  NEXT FROM CUR_VALUE INTO @ValueContent 

-- execute in loop 
WHILE @@FETCH_STATUS = 0
    BEGIN 
            SELECT @query = 'SELECT p.* 
                            INTO dbo.table_'+ @ValueContent  +'
                            FROM 
                           (SELECT  ProductReferenceID
                                    ,ColumnName
                                    ,Sort
                                    ,VersionID
                                    ,ValueText
                                    ,ValueDate
                                    ,ValueID
                                  FROM dbo.table
                                  where ProductReferenceID = 172958
                            )x 
                            PIVOT ( sum('+ @ValueContent  +') for columnname in (' + @cols +') ) P'
            SELECT @QUERY;
            SET @SQL = @QUERY
            EXEC (@SQL)

            FETCH NEXT FROM CUR_VALUE INTO @ValueContent

    END
    CLOSE CUR_VALUE
    DEALLOCATE CUR_VALUE

END 
GO