Преобразование строк в столбцы с помощью "Pivot" в SQL Server

Это для динамического # недель.

полный пример здесь: SQL Dynamic Pivot

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

Я достиг того же самого раньше, используя подзапросы. Поэтому, если ваша исходная таблица называлась StoreCountsByWeek, и у вас была отдельная таблица, в которой перечислены идентификаторы магазина, то она будет выглядеть так:

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

одним из преимуществ данного метода является то, что синтаксис более понятен и его легче присоединиться к другим таблицам, чтобы вытащить другие поля в поисковой.

мои анекдотические результаты заключаются в том, что выполнение этого запроса в течение нескольких тысяч строк завершено менее одной секунды, и у меня на самом деле было 7 подзапросов. Но, как отмечается в комментариях, это более вычислительно дорого сделать это таким образом, поэтому будьте осторожны с использованием этого метода, если вы ожидаете, что он будет работать на больших объемах данных .

вот что вы можете сделать:

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

демо

Я пишу 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;

затем Select * From ##TEMPORAL1PVT будет возвращение:

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

вот пересмотр ответа @Tayrn выше, который может помочь вам понять поворот немного проще:

это может быть не лучший способ сделать это, но это то, что помогло мне обернуть голову вокруг того, как сводные таблицы.

ID = строки, которые вы хотите повернуть

MY_KEY = столбец, который вы выбираете из исходной таблицы, содержащей имена столбцов, которые вы хотите повернуть.

VAL = значение, которое вы хотите вернуть под каждым колонна.

MAX (VAL) => может быть заменен другими агрегатными функциями. СУММА (ВАЛ), МИН(ВАЛ) И ДР...

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);