Преобразование строк в столбцы с помощью "Pivot" в SQL Server
Я прочитал материал в сводных таблицах MS, и у меня все еще есть проблемы с получением этого правильного.
У меня есть временная таблица, которая создается, мы скажем, что столбец 1 - это номер магазина, а столбец 2 - это номер недели, и, наконец, столбец 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
Если вы используете SQL Server 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 Demo
Затем, если вам нужно динамически генерировать номер недели, ваш код будет выглядеть следующим образом:
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 Demo.
Динамическая версия генерирует список чисел week, которые должны быть преобразованы в столбцы. Оба дают тот же результат:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
Ответ 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
Я уже делал то же самое, используя подзапросы. Итак, если ваша оригинальная таблица была вызвана 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 подзапросов. Но, как отмечалось в комментариях, для этого более дорогостоящим образом дорогостоящим образом, поэтому будьте осторожны при использовании этого метода, если вы ожидаете, что он будет работать на больших объемах данных.
Ответ 4
Это то, что вы можете сделать:
SELECT *
FROM yourTable
PIVOT (MAX(xCount)
FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt
DEMO
Ответ 5
Я пишу sp, который может быть полезен для этой цели, в основном это sp pivot любой таблицы и возвращает новую таблицу pivoted или возвращает только набор данных, это способ ее выполнения:
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 '[email protected]_table+'';
Exec (@query);
End;
Select @query='Select distinct '[email protected]+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)[email protected]+'.'[email protected]+' where '[email protected]+' 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('[email protected]_agg+'(case when '[email protected]+'='+quotename(@pivot_col,char(39))+' then '[email protected]_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 @[email protected][email protected]_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 '[email protected]_cols+','[email protected]_query_pivot+' into '[email protected]_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
@schema+'.'[email protected]+' Group by '[email protected]_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 вернет:
![enter image description here]()
Ответ 6
select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;
Ответ 7
Вот пересмотренный ответ @Tayrn выше, который может помочь вам немного легче понять поворот:
Возможно, это не лучший способ сделать это, но именно это помогло мне обернуться, как поворачивать столы.
ID = строки, которые вы хотите повернуть
MY_KEY = столбец, который вы выбираете из исходной таблицы и который содержит имена столбцов, которые вы хотите изменить.
VAL = значение, которое вы хотите вернуть под каждым столбцом.
MAX (VAL) => Может быть заменен другими агрегатными функциями. Сумма (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
Просто дайте вам некоторое представление о том, как другие базы данных решают эту проблему. DolphinDB также имеет встроенную поддержку для поворота, и SQL выглядит гораздо более интуитивно понятным и аккуратным. Это так же просто, как указание ключевого столбца (Store), поворотного столбца (Week) и вычисленной метрики (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 мс на ноутбуке dell xps (i7 cpu). Для получения более подробной информации, пожалуйста, обратитесь к онлайн-руководству DolphinDB https://www.dolphindb.com/help/index.html?pivotby.html.
