首页 文章

在sql server中有效地将行转换为列

提问于
浏览
292

我正在寻找一种有效的方法将行转换为SQL服务器中的列,我听说PIVOT不是很快,我需要处理大量的记录 .

这是我的例子:

-------------------------------
   | Id | Value  | ColumnName    |
   -------------------------------
   | 1  | John   | FirstName     |
   | 2  | 2.4    | Amount        |
   | 3  | ZH1E4A | PostalCode    |
   | 4  | Fork   | LastName      |
   | 5  | 857685 | AccountNumber |
   -------------------------------

这是我的结果:

---------------------------------------------------------------------
| FirstName  |Amount|   PostalCode   |   LastName  |  AccountNumber |
---------------------------------------------------------------------
| John       | 2.4  |   ZH1E4A       |   Fork      |  857685        |
---------------------------------------------------------------------

我该如何 Build 结果?

2 回答

  • 430

    有几种方法可以将多行中的数据转换为列 . 在SQL Server中,您可以使用 PIVOT 函数将数据从行转换为列:

    select Firstname, Amount, PostalCode, LastName, AccountNumber
    from
    (
      select value, columnname
      from yourtable
    ) d
    pivot
    (
      max(value)
      for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
    ) piv;
    

    Demo .

    如果您想要转置未知数量的 columnnames ,则可以使用动态SQL:

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                        from yourtable
                        group by ColumnName, id
                        order by id
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = N'SELECT ' + @cols + N' from 
                 (
                    select value, ColumnName
                    from yourtable
                ) x
                pivot 
                (
                    max(value)
                    for ColumnName in (' + @cols + N')
                ) p '
    
    exec sp_executesql @query;
    

    Demo .

    如果您不想使用 PIVOT 函数,则可以使用带有 CASE 表达式的聚合函数:

    select
      max(case when columnname = 'FirstName' then value end) Firstname,
      max(case when columnname = 'Amount' then value end) Amount,
      max(case when columnname = 'PostalCode' then value end) PostalCode,
      max(case when columnname = 'LastName' then value end) LastName,
      max(case when columnname = 'AccountNumber' then value end) AccountNumber
    from yourtable
    

    Demo .

    这也可以使用多个连接来完成,但是您需要一些列来关联样本数据中没有的每一行 . 但基本语法是:

    select fn.value as FirstName,
      a.value as Amount,
      pc.value as PostalCode,
      ln.value as LastName,
      an.value as AccountNumber
    from yourtable fn
    left join yourtable a
      on fn.somecol = a.somecol
      and a.columnname = 'Amount'
    left join yourtable pc
      on fn.somecol = pc.somecol
      and pc.columnname = 'PostalCode'
    left join yourtable ln
      on fn.somecol = ln.somecol
      and ln.columnname = 'LastName'
    left join yourtable an
      on fn.somecol = an.somecol
      and an.columnname = 'AccountNumber'
    where fn.columnname = 'Firstname'
    
  • 7

    由于旋转数据仍然很热,我决定从中添加一些东西 . 这是一种方法,而不仅仅是一个脚本,但为您提供了更多的可能性 . 首先需要部署3个脚本:1)用户定义的TABLE类型[ ColumnActionList ] - >保存数据作为参数2)SP [ proc_PivotPrepare ] - >准备我们的数据3)SP [ proc_PivotExecute ] - >执行脚本

    CREATE TYPE [dbo].[ColumnActionList] AS TABLE(
            [ID] [smallint] NOT NULL,
            [ColumnName] [nvarchar](128) NOT NULL,
            [Action] [nchar](1) NOT NULL
        );
    GO
    
        CREATE PROCEDURE [dbo].[proc_PivotPrepare] 
        (
        @DB_Name        nvarchar(128),
        @TableName      nvarchar(128)
        )
        AS
        ----------------------------------------------------------------------------------------------------
        -----| Author: Bartosz
        ----------------------------------------------------------------------------------------------------
        SELECT @DB_Name = ISNULL(@DB_Name,db_name())
        DECLARE @SQL_Code nvarchar(max)
    
        DECLARE @MyTab TABLE (ID smallint identity(1,1), [Column_Name] nvarchar(128), [Type] nchar(1), [Set Action SQL] nvarchar(max));
    
        SELECT @SQL_Code        =   'SELECT [<| SQL_Code |>] = '' '' '
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''-----| Declare user defined type [ID] / [ColumnName] / [PivotAction] '' '
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''DECLARE @ColumnListWithActions ColumnActionList;'''
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''-----| Set [PivotAction] (''''S'''' as default) to select dimentions and values '' '
                                            + 'UNION ALL '
                                            + 'SELECT ''-----|'''
                                            + 'UNION ALL '
                                            + 'SELECT ''-----| ''''S'''' = Stable column || ''''D'''' = Dimention column || ''''V'''' = Value column '' '
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''INSERT INTO  @ColumnListWithActions VALUES ('' + CAST( ROW_NUMBER() OVER (ORDER BY [NAME]) as nvarchar(10)) + '', '' + '''''''' + [NAME] + ''''''''+ '', ''''S'''');'''
                                            + 'FROM [' + @DB_Name + '].sys.columns  '
                                            + 'WHERE object_id = object_id(''[' + @DB_Name + ']..[' + @TableName + ']'') '
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''-----| Execute sp_PivotExecute with parameters: columns and dimentions and main table name'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '
                                            + 'UNION ALL '
                                            + 'SELECT ''EXEC [dbo].[sp_PivotExecute] @ColumnListWithActions, ' + '''''' + @TableName + '''''' + ';'''
                                            + 'UNION ALL '
                                            + 'SELECT ''----------------------------------------------------------------------------------------------------'' '                            
         EXECUTE SP_EXECUTESQL @SQL_Code;
    
    GO
    CREATE PROCEDURE [dbo].[proc_PivotExecute]
    (
    @ColumnListWithActions  ColumnActionList ReadOnly
    ,@TableName                     nvarchar(128)
    )
    AS
    --#######################################################################################################################
    --###| Author: Bartosz
    --#######################################################################################################################
    
    
    --#######################################################################################################################
    --###| Step 1 - Select our user-defined-table-variable into temp table
    --#######################################################################################################################
    
    IF OBJECT_ID('tempdb.dbo.#ColumnListWithActions', 'U') IS NOT NULL DROP TABLE #ColumnListWithActions; 
    SELECT * INTO #ColumnListWithActions FROM @ColumnListWithActions;
    
    --#######################################################################################################################
    --###| Step 2 - Preparing lists of column groups as strings:
    --#######################################################################################################################
    
    DECLARE @ColumnName                     nvarchar(128)
    DECLARE @Destiny                        nchar(1)
    
    DECLARE @ListOfColumns_Stable           nvarchar(max)
    DECLARE @ListOfColumns_Dimension    nvarchar(max)
    DECLARE @ListOfColumns_Variable     nvarchar(max)
    --############################
    --###| Cursor for List of Stable Columns
    --############################
    
    DECLARE ColumnListStringCreator_S CURSOR FOR
    SELECT      [ColumnName]
    FROM        #ColumnListWithActions
    WHERE       [Action] = 'S'
    OPEN ColumnListStringCreator_S;
    FETCH NEXT FROM ColumnListStringCreator_S
    INTO @ColumnName
      WHILE @@FETCH_STATUS = 0
    
       BEGIN
            SELECT @ListOfColumns_Stable = ISNULL(@ListOfColumns_Stable, '') + ' [' + @ColumnName + '] ,';
            FETCH NEXT FROM ColumnListStringCreator_S INTO @ColumnName
       END
    
    CLOSE ColumnListStringCreator_S;
    DEALLOCATE ColumnListStringCreator_S;
    
    --############################
    --###| Cursor for List of Dimension Columns
    --############################
    
    DECLARE ColumnListStringCreator_D CURSOR FOR
    SELECT      [ColumnName]
    FROM        #ColumnListWithActions
    WHERE       [Action] = 'D'
    OPEN ColumnListStringCreator_D;
    FETCH NEXT FROM ColumnListStringCreator_D
    INTO @ColumnName
      WHILE @@FETCH_STATUS = 0
    
       BEGIN
            SELECT @ListOfColumns_Dimension = ISNULL(@ListOfColumns_Dimension, '') + ' [' + @ColumnName + '] ,';
            FETCH NEXT FROM ColumnListStringCreator_D INTO @ColumnName
       END
    
    CLOSE ColumnListStringCreator_D;
    DEALLOCATE ColumnListStringCreator_D;
    
    --############################
    --###| Cursor for List of Variable Columns
    --############################
    
    DECLARE ColumnListStringCreator_V CURSOR FOR
    SELECT      [ColumnName]
    FROM        #ColumnListWithActions
    WHERE       [Action] = 'V'
    OPEN ColumnListStringCreator_V;
    FETCH NEXT FROM ColumnListStringCreator_V
    INTO @ColumnName
      WHILE @@FETCH_STATUS = 0
    
       BEGIN
            SELECT @ListOfColumns_Variable = ISNULL(@ListOfColumns_Variable, '') + ' [' + @ColumnName + '] ,';
            FETCH NEXT FROM ColumnListStringCreator_V INTO @ColumnName
       END
    
    CLOSE ColumnListStringCreator_V;
    DEALLOCATE ColumnListStringCreator_V;
    
    SELECT @ListOfColumns_Variable      = LEFT(@ListOfColumns_Variable, LEN(@ListOfColumns_Variable) - 1);
    SELECT @ListOfColumns_Dimension = LEFT(@ListOfColumns_Dimension, LEN(@ListOfColumns_Dimension) - 1);
    SELECT @ListOfColumns_Stable            = LEFT(@ListOfColumns_Stable, LEN(@ListOfColumns_Stable) - 1);
    
    --#######################################################################################################################
    --###| Step 3 - Preparing table with all possible connections between Dimension columns excluding NULLs
    --#######################################################################################################################
    DECLARE @DIM_TAB TABLE ([DIM_ID] smallint, [ColumnName] nvarchar(128))
    INSERT INTO @DIM_TAB 
    SELECT [DIM_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'D';
    
    DECLARE @DIM_ID smallint;
    SELECT      @DIM_ID = 1;
    
    
    DECLARE @SQL_Dimentions nvarchar(max);
    
    IF OBJECT_ID('tempdb.dbo.##ALL_Dimentions', 'U') IS NOT NULL DROP TABLE ##ALL_Dimentions; 
    
    SELECT @SQL_Dimentions      = 'SELECT ID = ROW_NUMBER() OVER (ORDER BY ' + @ListOfColumns_Dimension + '), ' + @ListOfColumns_Dimension
                                                + ' INTO ##ALL_Dimentions '
                                                + ' FROM (SELECT DISTINCT' + @ListOfColumns_Dimension + ' FROM  ' + @TableName
                                                + ' WHERE ' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @DIM_ID) + ' IS NOT NULL ';
                                                SELECT @DIM_ID = @DIM_ID + 1;
                WHILE @DIM_ID <= (SELECT MAX([DIM_ID]) FROM @DIM_TAB)
                BEGIN
                SELECT @SQL_Dimentions = @SQL_Dimentions + 'AND ' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @DIM_ID) +  ' IS NOT NULL ';
                SELECT @DIM_ID = @DIM_ID + 1;
                END
    
    SELECT @SQL_Dimentions   = @SQL_Dimentions + ' )x';
    
    EXECUTE SP_EXECUTESQL  @SQL_Dimentions;
    
    --#######################################################################################################################
    --###| Step 4 - Preparing table with all possible connections between Stable columns excluding NULLs
    --#######################################################################################################################
    DECLARE @StabPos_TAB TABLE ([StabPos_ID] smallint, [ColumnName] nvarchar(128))
    INSERT INTO @StabPos_TAB 
    SELECT [StabPos_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'S';
    
    DECLARE @StabPos_ID smallint;
    SELECT      @StabPos_ID = 1;
    
    
    DECLARE @SQL_MainStableColumnTable nvarchar(max);
    
    IF OBJECT_ID('tempdb.dbo.##ALL_StableColumns', 'U') IS NOT NULL DROP TABLE ##ALL_StableColumns; 
    
    SELECT @SQL_MainStableColumnTable       = 'SELECT ID = ROW_NUMBER() OVER (ORDER BY ' + @ListOfColumns_Stable + '), ' + @ListOfColumns_Stable
                                                + ' INTO ##ALL_StableColumns '
                                                + ' FROM (SELECT DISTINCT' + @ListOfColumns_Stable + ' FROM  ' + @TableName
                                                + ' WHERE ' + (SELECT [ColumnName] FROM @StabPos_TAB WHERE [StabPos_ID] = @StabPos_ID) + ' IS NOT NULL ';
                                                SELECT @StabPos_ID = @StabPos_ID + 1;
                WHILE @StabPos_ID <= (SELECT MAX([StabPos_ID]) FROM @StabPos_TAB)
                BEGIN
                SELECT @SQL_MainStableColumnTable = @SQL_MainStableColumnTable + 'AND ' + (SELECT [ColumnName] FROM @StabPos_TAB WHERE [StabPos_ID] = @StabPos_ID) +  ' IS NOT NULL ';
                SELECT @StabPos_ID = @StabPos_ID + 1;
                END
    
    SELECT @SQL_MainStableColumnTable    = @SQL_MainStableColumnTable + ' )x';
    
    EXECUTE SP_EXECUTESQL  @SQL_MainStableColumnTable;
    
    --#######################################################################################################################
    --###| Step 5 - Preparing table with all options ID
    --#######################################################################################################################
    
    DECLARE @FULL_SQL_1 NVARCHAR(MAX)
    SELECT @FULL_SQL_1 = ''
    
    DECLARE @i smallint
    
    IF OBJECT_ID('tempdb.dbo.##FinalTab', 'U') IS NOT NULL DROP TABLE ##FinalTab; 
    
    SELECT @FULL_SQL_1 = 'SELECT t.*, dim.[ID] '
                                        + ' INTO ##FinalTab '
                                        +   'FROM ' + @TableName + ' t '
                                        +   'JOIN ##ALL_Dimentions dim '
                                        +   'ON t.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = 1) + ' = dim.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = 1);
                                    SELECT @i = 2                               
                                    WHILE @i <= (SELECT MAX([DIM_ID]) FROM @DIM_TAB)
                                        BEGIN
                                        SELECT @FULL_SQL_1 = @FULL_SQL_1 + ' AND t.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @i) + ' = dim.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @i)
                                        SELECT @i = @i +1
                                    END
    EXECUTE SP_EXECUTESQL @FULL_SQL_1
    
    --#######################################################################################################################
    --###| Step 6 - Selecting final data
    --#######################################################################################################################
    DECLARE @STAB_TAB TABLE ([STAB_ID] smallint, [ColumnName] nvarchar(128))
    INSERT INTO @STAB_TAB 
    SELECT [STAB_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName]
    FROM #ColumnListWithActions WHERE [Action] = 'S';
    
    DECLARE @VAR_TAB TABLE ([VAR_ID] smallint, [ColumnName] nvarchar(128))
    INSERT INTO @VAR_TAB 
    SELECT [VAR_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName]
    FROM #ColumnListWithActions WHERE [Action] = 'V';
    
    DECLARE @y smallint;
    DECLARE @x smallint;
    DECLARE @z smallint;
    
    
    DECLARE @FinalCode nvarchar(max)
    
    SELECT @FinalCode = ' SELECT ID1.*'
                                            SELECT @y = 1
                                            WHILE @y <= (SELECT MAX([ID]) FROM ##FinalTab)
                                                BEGIN
                                                    SELECT @z = 1
                                                    WHILE @z <= (SELECT MAX([VAR_ID]) FROM @VAR_TAB)
                                                        BEGIN
                                                            SELECT @FinalCode = @FinalCode +    ', [ID' + CAST((@y) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @VAR_TAB WHERE [VAR_ID] = @z) + '] =  ID' + CAST((@y + 1) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @VAR_TAB WHERE [VAR_ID] = @z)
                                                            SELECT @z = @z + 1
                                                        END
                                                        SELECT @y = @y + 1
                                                    END
            SELECT @FinalCode = @FinalCode + 
                                            ' FROM ( SELECT * FROM ##ALL_StableColumns)ID1';
                                            SELECT @y = 1
                                            WHILE @y <= (SELECT MAX([ID]) FROM ##FinalTab)
                                            BEGIN
                                                SELECT @x = 1
                                                SELECT @FinalCode = @FinalCode 
                                                                                    + ' LEFT JOIN (SELECT ' +  @ListOfColumns_Stable + ' , ' + @ListOfColumns_Variable 
                                                                                    + ' FROM ##FinalTab WHERE [ID] = ' 
                                                                                    + CAST(@y as varchar(10)) + ' )ID' + CAST((@y + 1) as varchar(10))  
                                                                                    + ' ON 1 = 1' 
                                                                                    WHILE @x <= (SELECT MAX([STAB_ID]) FROM @STAB_TAB)
                                                                                    BEGIN
                                                                                        SELECT @FinalCode = @FinalCode + ' AND ID1.' + (SELECT [ColumnName] FROM @STAB_TAB WHERE [STAB_ID] = @x) + ' = ID' + CAST((@y+1) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @STAB_TAB WHERE [STAB_ID] = @x)
                                                                                        SELECT @x = @x +1
                                                                                    END
                                                SELECT @y = @y + 1
                                            END
    
    SELECT * FROM ##ALL_Dimentions;
    EXECUTE SP_EXECUTESQL @FinalCode;
    --#######################################################################################################################
    

    从执行第一个查询(通过传递源数据库和表名),您将获得第二个SP的预先创建的执行查询,您所要做的就是定义来自您的源的列:稳定值(将用于集中基于的值)Dim(要用于转动的列)

    名称和数据类型将自动定义!

    我不能在任何 生产环境 环境中推荐它,但是为adhoc BI请求做了工作 .

相关问题