복붙노트

[SQL] 효율적으로 SQL 서버에 열을 행으로 변환

SQL

효율적으로 SQL 서버에 열을 행으로 변환

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

어떻게 결과를 만들 수 있습니까?

해결법

  1. ==============================

    1.당신이 컬럼에 여러 행에서 데이터를 변환 할 수있는 몇 가지 방법이 있습니다.

    당신이 컬럼에 여러 행에서 데이터를 변환 할 수있는 몇 가지 방법이 있습니다.

    SQL 서버에서는 컬럼에 행에서 데이터를 변환하기 위해 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;
    

    데모를 참조하십시오.

    당신이 트랜스에 당신이 원하는 것을 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;
    

    데모를 참조하십시오.

    당신은 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
    

    데모를 참조하십시오.

    이것은 또한 조인 다중를 사용하여 완료 할 수 있지만, 당신은 당신이 당신의 샘플 데이터가없는 각 행을 연결하는 데 약간의 열이 필요합니다. 그러나 기본 구문은 다음과 같습니다

    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'
    
  2. ==============================

    2.데이터를 선회하는 것은 여전히 ​​뜨거운 하나 같이 뭔가 양식을 저를 추가하기로 결정했다. 이 아니라 단지 하나의 스크립트보다 방법입니다하지만 당신에게 더 많은 가능성을 제공합니다. 우선 3 스크립트를 배포 할 필요가있다 : 1) 사용자 정의 테이블 형식 [ColumnActionList] -> 파라미터와 같은 데이터를 보유 2) SP는 [proc_PivotPrepare은] -> 우리의 데이타를 준비 3) SP [proc_PivotExecute] -> 스크립트를 실행할

    데이터를 선회하는 것은 여전히 ​​뜨거운 하나 같이 뭔가 양식을 저를 추가하기로 결정했다. 이 아니라 단지 하나의 스크립트보다 방법입니다하지만 당신에게 더 많은 가능성을 제공합니다. 우선 3 스크립트를 배포 할 필요가있다 : 1) 사용자 정의 테이블 형식 [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;
    --#######################################################################################################################
    

    (소스 DB 및 테이블 이름을 전달하여) 첫 번째 쿼리를 실행에서 당신은 당신이 할 필요가 모든 소스에서 열이 정의 두 번째 SP에 대한 사전 생성 된 실행 쿼리를 얻을 것이다 : + 안정 + 값 (즉, 농축에 기초 값을 사용한다) + 딤섬 (열이가 피벗에 사용하려는)

    이름 및 데이터 유형이 자동으로 정의됩니다!

    나는 어떤 생산 환경을위한 추천하지만 애드혹 BI 요청에 대해 작업을 수행하지 못할.

  3. from https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server by cc-by-sa and MIT license