Konvertieren Sie Zeilen in Spalten mithilfe von "Pivot" in SQL Server
Ich habe das Zeug auf MS Pivot-Tabellen gelesen und habe immer noch Probleme, dies zu korrigieren.
Ich habe eine temporäre Tabelle, die gerade erstellt wird. Wir werden sagen, dass Spalte 1 eine Geschäftsnummer ist und Spalte 2 eine Wochennummer und zuletzt Spalte 3 eine Art Gesamtzahl. Auch die Wochennummern sind dynamisch, die Geschäftsnummern sind statisch.
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
Ich möchte, dass es als Pivot-Tabelle herauskommt:
Store 1 2 3 4 5 6....
-----
101 138 282 220
102 96 212 123
105 37
109
Speichern Sie Zahlen auf der Seite und Wochen auf der Oberseite.
Wenn Sie SQL Server 2005+ verwenden, können Sie die Funktion PIVOT verwenden, um die Daten von Zeilen in Spalten umzuwandeln.
Es hört sich so an, als müssten Sie dynamisches SQL verwenden, wenn die Wochen unbekannt sind, aber es ist einfacher, den richtigen Code zunächst mit einer hartcodierten Version zu sehen.
Hier sind zunächst einige schnelle Tabellendefinitionen und Daten zur Verwendung:
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);
Wenn Ihre Werte bekannt sind, werden Sie die Abfrage hart codieren:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
Siehe SQL Demo
Wenn Sie dann die Wochennummer dynamisch generieren müssen, lautet Ihr Code:
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);
Siehe SQL Demo .
Die dynamische Version generiert die Liste der week Nummern, die in Spalten konvertiert werden sollen. Beide geben das gleiche Ergebnis:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
Dies ist für die dynamische Anzahl von Wochen.
Vollständiges Beispiel hier: 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
Ich habe dasselbe bereits zuvor mit Unterabfragen erreicht. Wenn Ihre Originaltabelle also StoreCountsByWeek heißt und Sie eine separate Tabelle haben, in der die Geschäfts-IDs aufgeführt sind, sieht sie folgendermaßen aus:
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
Ein Vorteil dieser Methode ist, dass die Syntax klarer ist und das Verknüpfen mit anderen Tabellen erleichtert wird, um auch andere Felder in die Ergebnisse zu ziehen.
Meine anekdotischen Ergebnisse sind, dass diese Abfrage über ein paar tausend Zeilen ausgeführt wurde, die in weniger als einer Sekunde ausgeführt wurden, und ich hatte tatsächlich 7 Unterabfragen. Wie in den Kommentaren erwähnt, ist dies jedoch rechenintensiver. Seien Sie also vorsichtig, wenn Sie davon ausgehen, dass diese Methode mit großen Datenmengen ausgeführt wird.
Das können Sie tun:
SELECT *
FROM yourTable
PIVOT (MAX(xCount)
FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt
Ich schreibe ein SP, das für diesen Zweck nützlich sein könnte. Im Grunde genommen dreht dieses SP jede Tabelle und gibt eine neue geschwenkte Tabelle zurück oder gibt nur den Datensatz zurück. Dies ist die Art und Weise, es auszuführen:
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;
Bitte beachten Sie , dass im Parameter @agg die Spaltennamen mit '[' und der Parameter mit einem Komma ',' enden müssen.
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
Dies ist ein Beispiel für die Ausführung:
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;
dann würde Select * From ##TEMPORAL1PVT zurückkehren:
select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;
Geben Sie einfach eine Vorstellung davon, wie andere Datenbanken dieses Problem lösen. DolphinDB hat auch eine eingebaute Unterstützung für das Schwenken und der SQL sieht viel intuitiver und ordentlicher aus. Sie müssen lediglich die Schlüsselspalte (Store), die Pivot-Spalte (Week) und die berechnete Metrik (sum(xCount)) angeben.
//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 ist eine kolumnare Hochleistungsdatenbank. Die Berechnung in der Demo kostet auf einem Dell xps Laptop (i7 cpu) nur 546 ms. Weitere Informationen finden Sie im Online-Handbuch zu DolphinDB https://www.dolphindb.com/help/index.html?pivotby.html
Hier ist eine Überarbeitung der obigen @Tayrn-Antwort, die Ihnen helfen könnte, das Schwenken ein wenig einfacher zu verstehen:
Dies ist möglicherweise nicht der beste Weg, dies zu tun, aber dies hat mir geholfen, meinen Kopf darum zu wickeln, wie Tische gedreht werden.
ID = Zeilen, die Sie schwenken möchten
MY_KEY = Die Spalte, die Sie aus Ihrer Originaltabelle auswählen und die Spaltennamen enthält, die Sie pivoten möchten.
VAL = der Wert, den Sie unter jeder Spalte zurückgeben möchten.
MAX (VAL) => Kann durch andere Aggregatfunktionen ersetzt werden. SUMME (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);