복붙노트

[SQL] SQL Server의 '피봇'를 사용하여 열을 변환 행

SQL

SQL Server의 '피봇'를 사용하여 열을 변환 행

나는 MS의 피벗 테이블에 물건을 읽고 난 여전히 문제가이 올바른을 얻는 데.

내가 생성되는 임시 테이블을 가지고, 우리는 1 열은 저장 번호라고하며, 열이 일주일 번호와 마지막 3 열 일부 유형의 총입니다. 또한, 주 번호는 동적이며, 매장 수는 정적이다.

Store      Week     xCount
-------    ----     ------
102        1        96
101        1        138
105        1        37
109        1        59
101        2        282
102        2        212
105        2        78
109        2        97
105        3        60
102        3        123
101        3        220
109        3        87

나는 다음과 같이 피벗 테이블로 나올 좋아합니다 :

Store        1          2          3        4        5        6....
----- 
101        138        282        220
102         96        212        123
105         37        
109

상단에 저장 측면 아래 번호와 주.

해결법

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

    1.당신은 SQL 서버 2005 +를 사용하는 경우, 당신은 행을 열로에서 데이터를 변환하기 위해 PIVOT 기능을 사용할 수 있습니다.

    당신은 SQL 서버 2005 +를 사용하는 경우, 당신은 행을 열로에서 데이터를 변환하기 위해 PIVOT 기능을 사용할 수 있습니다.

    당신이 주를 알 수 있지만, 처음에 하드 코딩 된 버전을 사용하여 올바른 코드를 쉽게 볼 경우 동적 SQL을 사용해야합니다 것 같은데.

    첫번째로, 여기에 사용하기위한 몇 가지 빠른 테이블 정의 및 데이터는 다음과 같습니다

    CREATE TABLE #yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO #yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

    당신의 가치를 알고있는 경우에, 당신은 하드 코드 쿼리를 것입니다 :

    select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

    참조 SQL 데모

    동적으로 주 번호를 생성해야하는 경우 그런 다음 코드는 다음과 같습니다

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

    SQL 데모를 참조하십시오.

    동적 버전은 열을 변환해야 주 번호의 목록을 생성합니다. 모두 같은 결과를 제공합니다 :

    | STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |
  2. ==============================

    2.이 주 동적 #입니다.

    이 주 동적 #입니다.

    여기에 전체 예 : SQL 동적 피벗

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Store, ' + @ColumnName + ' 
    FROM #StoreSales
    PIVOT(SUM(xCount) 
          FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
  3. ==============================

    3.나는 하위 쿼리를 사용하여 이전에 같은 일을 달성했습니다. 원래 테이블이 StoreCountsByWeek라고, 당신이 스토어 ID를 나열 별도의 테이블을 있었다 그렇다면, 그 결과는 다음과 같습니다

    나는 하위 쿼리를 사용하여 이전에 같은 일을 달성했습니다. 원래 테이블이 StoreCountsByWeek라고, 당신이 스토어 ID를 나열 별도의 테이블을 있었다 그렇다면, 그 결과는 다음과 같습니다

    SELECT StoreID, 
    Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
    Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
    Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID

    이 방법의 장점 중 하나는 구문이 더 명확하다는 것이다 그것은 쉽게 너무 결과에 다른 필드를 끌어 다른 테이블에 가입 할 수 있습니다.

    내 일화 결과는 1 초 미만에 완료 천 행의 몇 이상이 쿼리를 실행하고 나는 것은 실제로 7 개 하위 쿼리를하였습니다입니다. 코멘트에서 언급 한 바와 같이, 그것은 당신이 많은 양의 데이터에서 실행되도록 기대한다면 그래서이 방법을 사용하여 조심, 더 많은 계산 이런 식으로하는 것입니다.

  4. ==============================

    4.이것은 당신이 무엇을 할 수 있습니다 :

    이것은 당신이 무엇을 할 수 있습니다 :

    SELECT * 
FROM yourTable
PIVOT (MAX(xCount) 
       FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt

    데모

  5. ==============================

    5.나는,이 목적을 위해 모든 테이블 피벗 기본적으로이 SP에 유용 할 수 및 데이터의 단지 세트를 새 테이블이 선회 반환하거나 반환 할 수있는 SP를 쓰는 사람이 그것을 실행하는 방법은 다음과 같습니다

    나는,이 목적을 위해 모든 테이블 피벗 기본적으로이 SP에 유용 할 수 및 데이터의 단지 세트를 새 테이블이 선회 반환하거나 반환 할 수있는 SP를 쓰는 사람이 그것을 실행하는 방법은 다음과 같습니다

    Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg='sum([column_to_agg]),avg([another_column_to_agg]),',
        @sel_cols='column_to_select1,column_to_select2,column_to_select1',@new_table=returned_table_pivoted;

    제발 참고 열 이름 @agg 매개 변수에 '['와 함께해야하며, 매개 변수가 쉼표로 끝나야 ','

    SP

    Create Procedure [dbo].[rs_pivot_table]
    @schema sysname=dbo,
    @table sysname,
    @column sysname,
    @agg nvarchar(max),
    @sel_cols varchar(max),
    @new_table sysname,
    @add_to_col_name sysname=null
As
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
Begin

    Declare @query varchar(max)='';
    Declare @aggDet varchar(100);
    Declare @opp_agg varchar(5);
    Declare @col_agg varchar(100);
    Declare @pivot_col sysname;
    Declare @query_col_pvt varchar(max)='';
    Declare @full_query_pivot varchar(max)='';
    Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica

    Create Table #pvt_column(
        pivot_col varchar(100)
    );

    Declare @column_agg table(
        opp_agg varchar(5),
        col_agg varchar(100)
    );

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
        Set @ind_tmpTbl=0;
    ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
        Set @ind_tmpTbl=1;

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OR 
        OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL
    Begin
        Set @query='DROP TABLE '+@new_table+'';
        Exec (@query);
    End;

    Select @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';
    Print @query;

    Insert into #pvt_column(pivot_col)
    Exec (@query)

    While charindex(',',@agg,1)>0
    Begin
        Select @aggDet=Substring(@agg,1,charindex(',',@agg,1)-1);

        Insert Into @column_agg(opp_agg,col_agg)
        Values(substring(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));

        Set @agg=Substring(@agg,charindex(',',@agg,1)+1,len(@agg))

    End

    Declare cur_agg cursor read_only forward_only local static for
    Select 
        opp_agg,col_agg
    from @column_agg;

    Open cur_agg;

    Fetch Next From cur_agg
    Into @opp_agg,@col_agg;

    While @@fetch_status=0
    Begin

        Declare cur_col cursor read_only forward_only local static for
        Select 
            pivot_col 
        From #pvt_column;

        Open cur_col;

        Fetch Next From cur_col
        Into @pivot_col;

        While @@fetch_status=0
        Begin

            Select @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+
            ' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+
                (case when @add_to_col_name is null then space(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'
            print @query_col_pvt
            Select @full_query_pivot=@full_query_pivot+@query_col_pvt+', '

            --print @full_query_pivot

            Fetch Next From cur_col
            Into @pivot_col;        

        End     

        Close cur_col;
        Deallocate cur_col;

        Fetch Next From cur_agg
        Into @opp_agg,@col_agg; 
    End

    Close cur_agg;
    Deallocate cur_agg;

    Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);

    Select @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
    @schema+'.'+@table+' Group by '+@sel_cols+';';

    print @query;
    Exec (@query);

End;
GO

    이 실행의 예이다 :

    Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;

    그때부터 ## TEMPORAL1PVT 선택 *는 반환 :

  6. ==============================

    6.

    select * from (select name, ID from Empoyee) Visits
    pivot(sum(ID) for name
    in ([Emp1],
    [Emp2],
    [Emp3]
    ) ) as pivottable;
  7. ==============================

    7.여기에 당신이 쉽게 조금 선회 이해 힘 도움말 위 @Tayrn 응답의 개정은 다음과 같습니다

    여기에 당신이 쉽게 조금 선회 이해 힘 도움말 위 @Tayrn 응답의 개정은 다음과 같습니다

    이이 작업을 수행하는 가장 좋은 방법은되지 않을 수도 있습니다,하지만이 날 피벗 테이블 주위에 어떻게 내 머리를 정리하고 도움을 것입니다.

    ID는 피벗 할 행을 =

    MY_KEY 당신이 선회 할 열 이름을 포함하여 원래 테이블에서 선택하는 열을 =.

    VAL은 각 열에서 반환하려는 값을 =.

    MAX (VAL)는 => 다른 집계 함수로 대체 할 수 있습니다. SUM (VAL), MIN (VAL), ETC ...

    DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(MY_KEY) 
                from yt
                group by MY_KEY
                order by MY_KEY ASC
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
set @query = 'SELECT ID,' + @cols + ' from 
         (
            select ID, MY_KEY, VAL 
            from yt
        ) x
        pivot 
        (
            sum(VAL)
            for MY_KEY in (' + @cols + ')
        ) p '

        execute(@query);
  8. ==============================

    8.그냥 다른 데이터베이스가이 문제를 해결하는 방법을 몇 가지 아이디어를 제공합니다. DolphinDB 또한 선회에 대한 지원 내장했고 SQL은 훨씬 더 직관적이고 깔끔한 보인다. 이는 열 (주)에 회동 키 컬럼 (스토어)를 지정하는 것과 같이 간단하고, 계산 된 메트릭 (SUM (xCount)).

    그냥 다른 데이터베이스가이 문제를 해결하는 방법을 몇 가지 아이디어를 제공합니다. DolphinDB 또한 선회에 대한 지원 내장했고 SQL은 훨씬 더 직관적이고 깔끔한 보인다. 이는 열 (주)에 회동 키 컬럼 (스토어)를 지정하는 것과 같이 간단하고, 계산 된 메트릭 (SUM (xCount)).

    //prepare a 10-million-row table
n=10000000
t=table(rand(100, n) + 1 as Store, rand(54, n) + 1 as Week, rand(100, n) + 1 as xCount)

//use pivot clause to generate a pivoted table pivot_t
pivot_t = select sum(xCount) from t pivot by Store, Week

    DolphinDB는 원주 고성능 데이터베이스입니다. 델에 546 MS의 낮은 데모 비용 계산은 노트북 (I7 CPU)를 XPS. 더 자세한 정보를 얻으려면, https://www.dolphindb.com/help/index.html?pivotby.html 온라인 DolphinDB 설명서를 참조하십시오

    9. from https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왜 우리는 항상 SQL 문에서 매개 변수를 사용하여 선호합니까?  (0) 2020.03.05
[SQL] 관계형 데이터베이스에서 계층 적 데이터를 저장하기위한 옵션은 무엇입니까? [닫은]  (0) 2020.03.05
[SQL] GROUP BY에서 LIMIT를 사용하면 그룹 별 N 결과를 얻으려면?  (0) 2020.03.05
[SQL] 어떻게 FULL OUTER MySQL의에 가입 할까?  (0) 2020.03.05
[SQL] 효율적으로 SQL 서버에 열을 행으로 변환  (0) 2020.03.05
,

티스토리툴바