Konwertuj wiersze na kolumny za pomocą "Pivot" w SQL Server
Przeczytałem rzeczy na tablicach ms pivot i nadal mam problemy z poprawieniem tego.
Mam tabelę tymczasową, która jest tworzona, powiemy, że kolumna 1 to numer sklepu, a Kolumna 2 to numer tygodnia, a na koniec kolumna 3 to w sumie jakiś typ. Również numery tygodnia są dynamiczne, numery sklepów są statyczne.
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
Chciałbym, aby wyszła jako tabela przestawna, w ten sposób:
Store 1 2 3 4 5 6....
-----
101 138 282 220
102 96 212 123
105 37
109
Przechowuj numery z boku i tygodnie w poprzek na górze.
8 answers
Jeśli używasz SQL Server 2005+, możesz użyć funkcji PIVOT
do przekształcenia danych z wierszy w kolumny.
Wygląda na to, że będziesz musiał użyć dynamicznego sql, jeśli tygodnie są nieznane, ale łatwiej jest zobaczyć poprawny kod przy użyciu hard-zakodowanej wersji początkowo.
Po pierwsze, oto kilka szybkich definicji tabeli i danych do użycia:
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);
Jeśli twoje wartości są znane, to Utwórz kod zapytania:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
Zobacz SQL Demo
Następnie, jeśli chcesz wygenerować numer tygodnia dynamicznie, Twój kod będzie:
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);
Zobacz SQL Demo.
Wersja dynamiczna, generuje listę week
liczb, które powinny zostać przekonwertowane na kolumny. Oba dają ten sam wynik:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-29 12:51:53
To jest dynamiczne # tygodni.
Pełny przykład tutaj: 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-16 10:20:22
Osiągnąłem to samo wcześniej używając zapytań podrzędnych. Jeśli więc twoja oryginalna tabela nosiła nazwę StoreCountsByWeek, a ty miałeś oddzielną tabelę, która zawierała identyfikatory sklepów, wyglądałaby tak:
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
Zaletą tej metody jest to, że składnia jest bardziej przejrzysta i ułatwia łączenie się z innymi tabelami, aby przyciągnąć inne pola do wyników.
Moje anegdotyczne wyniki są takie, że uruchamianie tego zapytania przez kilka tysięcy wierszy zakończonych w mniej niż jednym po drugie, i faktycznie miałem 7 subqueries. Ale jak wspomniano w komentarzach, wykonanie tego w ten sposób jest bardziej kosztowne obliczeniowo, więc uważaj na używanie tej metody, jeśli spodziewasz się, że będzie działać na dużych ilościach danych .
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-30 13:55:44
Oto co możesz zrobić:
SELECT *
FROM yourTable
PIVOT (MAX(xCount)
FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-04-10 16:46:20
Piszę sp, który może być przydatny w tym celu, w zasadzie ten SP obraca dowolną tabelę i zwraca nową tabelę obróconą lub zwraca tylko zestaw danych, w ten sposób można ją wykonać:
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;
Proszę zauważyć, że w parametrze @ agg nazwy kolumn muszą być z '['
, a parametr musi kończyć się przecinkiem ','
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
Oto przykład wykonania:
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;
Wtedy Select * From ##TEMPORAL1PVT
zwróci:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-30 14:35:41
Oto przegląd odpowiedzi @Tayrn powyżej, który może pomóc ci zrozumieć obracanie się trochę łatwiej:
To może nie jest najlepszy sposób, aby to zrobić, ale to jest to, co pomogło mi owinąć głowę wokół jak obracać tabele.
ID = rows you want to pivot
MY_KEY = kolumna, którą wybierasz z oryginalnej tabeli, która zawiera nazwy kolumn, które chcesz obrócić.
VAL = wartość, którą chcesz zwrócić pod każdą kolumną.
MAX (VAL) = > można zastąpić przez inne funkcje agregatu. SUM (VAL), MIN (VAL) ITD...
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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-12 13:37:35
select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-28 14:06:50
Po Prostu dać ci jakiś pomysł, jak inne bazy danych rozwiązać ten problem. DolphinDB
posiada również wbudowaną obsługę pivotingu, a sql wygląda znacznie bardziej intuicyjnie i schludnie. Jest to tak proste, jak określenie kolumny kluczowej (Store
), kolumny obrotowej (Week
) i obliczonej metryki (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
Dolfindb jest kolumnową bazą danych o wysokiej wydajności. Obliczenia w wersji demo kosztują zaledwie 546 ms na laptopie dell xps (cpu i7). Aby uzyskać więcej informacji, zapoznaj się z instrukcją Dolfindb Online https://www.dolphindb.com/help/index.html?pivotby.html
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-05-11 01:18:52