Ik ben erg nieuw in SQL.
Ik heb een tabel als deze:
ID | TeamID | UserID | ElementID | PhaseID | Effort
-----------------------------------------------------
1 | 1 | 1 | 3 | 5 | 6.74
2 | 1 | 1 | 3 | 6 | 8.25
3 | 1 | 1 | 4 | 1 | 2.23
4 | 1 | 1 | 4 | 5 | 6.8
5 | 1 | 1 | 4 | 6 | 1.5
En ik kreeg te horen dat ik dergelijke gegevens moest krijgen
ElementID | PhaseID1 | PhaseID5 | PhaseID6
--------------------------------------------
3 | NULL | 6.74 | 8.25
4 | 2.23 | 6.8 | 1.5
Ik begrijp dat ik de PIVOT-functie moet gebruiken. Maar kan het niet duidelijk begrijpen.
Het zou geweldig zijn als iemand het in het bovenstaande geval kan uitleggen (of eventuele alternatieven)
Antwoord 1, autoriteit 100%
Een pivotdie wordt gebruikt om te roteren de gegevens van één kolom naar meerdere kolommen.
Voor uw voorbeeld is hier een STATIC Pivot, wat betekent dat u de kolommen die u wilt roteren hard codeert:
create table temp
(
id int,
teamid int,
userid int,
elementid int,
phaseid int,
effort decimal(10, 5)
)
insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)
select elementid
, [1] as phaseid1
, [5] as phaseid5
, [6] as phaseid6
from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in([1], [5], [6])
)p
Hier is een SQL-demomet een werkende versie.
Dit kan ook worden gedaan via een dynamische PIVOT, waarbij u de lijst met kolommen dynamisch maakt en de PIVOT uitvoert.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT elementid, ' + @cols + ' from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in (' + @cols + ')
) p '
execute(@query)
De resultaten voor beide:
ELEMENTID PHASEID1 PHASEID5 PHASEID6
3 Null 6.74 8.25
4 2.23 6.8 1.5
Antwoord 2, autoriteit 7%
Dit zijn de zeer eenvoudige spilvoorbeelden, neem dat alstublieft door.
SQL SERVER – PIVOT en Voorbeelden van UNPIVOT-tabel
Voorbeeld van bovenstaande link voor de producttabel:
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
weergave:
PRODUCT FRED KATE
--------------------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5
Vergelijkbare voorbeelden zijn te vinden in de blogpost draaitabellen in SQL Server. Een eenvoudig voorbeeld
Antwoord 3, autoriteit 6%
Ik wil hier iets toevoegen dat niemand heeft genoemd.
De functie pivotwerkt prima als de bron drie kolommen heeft: één voor de aggregate, één om als kolommen te verspreiden met for, en één als een spil voor de distributie van row. In het productvoorbeeld is dit QTY, CUST, PRODUCT.
Als u echter meer kolommen in de bron heeft, worden de resultaten opgesplitst in meerdere rijen in plaats van één rij per draaipunt op basis van unieke waarden per extra kolom (zoals Group Byzou doen in een eenvoudige vraag).
Zie dit voorbeeld, ik heb een tijdstempelkolom aan de brontabel toegevoegd:
Bekijk nu de impact:
SELECT CUST, MILK
FROM Product
-- FROM (SELECT CUST, Product, QTY FROM PRODUCT) p
PIVOT (
SUM(QTY) FOR PRODUCT IN (MILK)
) AS pvt
ORDER BY CUST
Om dit op te lossen, kun je ofwel een subquery als bron gebruiken, zoals iedereen hierboven heeft gedaan – met slechts 3 kolommen (dit werkt niet altijd voor jouw scenario, stel je voor dat je een wherevoorwaarde voor de tijdstempel).
Tweede oplossing is om een group byte gebruiken en de som van de gedraaide kolomwaarden opnieuw te doen.
SELECT
CUST,
sum(MILK) t_MILK
FROM Product
PIVOT (
SUM(QTY) FOR PRODUCT IN (MILK)
) AS pvt
GROUP BY CUST
ORDER BY CUST
GO
Antwoord 4, autoriteit 5%
Een pivot wordt gebruikt om een van de kolommen in uw dataset van rijen naar kolommen te converteren (dit wordt meestal de spreading columngenoemd). In het voorbeeld dat u hebt gegeven, betekent dit dat de rijen PhaseIDworden omgezet in een reeks kolommen, waarbij er één kolom is voor elke afzonderlijke waarde die PhaseIDkan bevatten – 1, 5 en 6 in dit geval.
Deze gedraaide waarden zijn gegroepeerdvia de kolom ElementIDin het voorbeeld dat u hebt gegeven.
Normaal gesproken moet u dan ook een vorm van aggregatieopgeven die u de waarden geeft waarnaar wordt verwezen door het snijpunt van de spreading value(PhaseID) en de groeperingswaarde(ElementID). Hoewel in het gegeven voorbeeld de aggregatiedie zal worden gebruikt onduidelijk is, maar de kolom Effortbetreft.
Zodra dit draaien is voltooid, worden de groeperingen spreadkolommengebruikt om een aggregatiewaardete vinden. Of in jouw geval, ElementIDen PhaseIDXzoek Effortop.
Als u de terminologie groeperen, verspreiden, aggregerengebruikt, ziet u de voorbeeldsyntaxis voor een spil als volgt:
WITH PivotData AS
(
SELECT <grouping column>
, <spreading column>
, <aggregation column>
FROM <source table>
)
SELECT <grouping column>, <distinct spreading values>
FROM PivotData
PIVOT (<aggregation function>(<aggregation column>)
FOR <spreading column> IN <distinct spreading values>));
Ditgeeft een grafische uitleg over hoe de kolommen groeperen, spreiden en aggregerenworden omgezet van de bron naar draaitabellen als dat verder helpt.
Antwoord 5, autoriteit 3%
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Here is the result set.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Antwoord 6, autoriteit 3%
Compatibiliteitsfout instellen
gebruik dit voordat u de draaifunctie gebruikt
ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100
Antwoord 7
VOOR XML PATHmogelijk niet werken aan Microsoft Azure Synapse Serve. Een mogelijk alternatief, volgens de @Taryn-dynamisch gegenereerde cols-benadering, worden dezelfde resultaten verkregen door STRING_AGG.
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT @cols = STRING_AGG(QUOTENAME(c.phaseid),', ')
/*OPTIONAL: within group (order by cast(t1.[FLOW_SP_SLPM] as INT) asc)*/
FROM (SELECT phaseid FROM temp
GROUP BY phaseid) c
set @query = 'SELECT elementid,' + @cols + ' from
(
select elementid,
phaseid,
effort
from temp
) x
PIVOT
(
max(effort)
for phaseid in (' + @cols + ')
) p '
execute(@query)