Hoe kan ik eenvoudig kolommen met rijen verwisselen in SQL? Is er een eenvoudig commando om te transponeren?
Ik bedoel, zet dit resultaat om:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
in dit:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
lijkt te complex voor dit scenario.
Er zijn verschillende manieren waarop u deze gegevens kunt transformeren. In uw oorspronkelijke post stelde u dat PIVOT
te complex lijkt voor dit scenario, maar het kan heel eenvoudig worden toegepast met behulp van zowel de UNPIVOT
en PIVOT
functies in SQL Server.
Echter, als u geen toegang heeft tot deze functies kan dit worden gerepliceerd met behulp van UNION ALL
naar UNPIVOT
en vervolgens een aggregatie functie met een CASE
statement naar PIVOT
:
Create Table:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Versie alle, aggregaat en CASE:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
De UNION ALL
voert de UNPIVOT
van de gegevens uit door de kolommen Paul, John, Tim, Eric
om te zetten in afzonderlijke rijen. Dan pas je de aggregatie functie sum()
toe met het case
statement om de nieuwe kolommen voor elke kleur
te krijgen.
Unpivot en Pivot Statische Versie:
Zowel de UNPIVOT
als de PIVOT
functies in SQL server maken deze transformatie veel eenvoudiger. Als u alle waarden kent die u wilt transformeren, kunt u ze hardcoderen in een statische versie om het resultaat te krijgen:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
De binnenste query met de UNPIVOT
voert dezelfde functie uit als de UNION ALL
. Het neemt de lijst van kolommen en maakt er rijen van, de PIVOT
voert dan de uiteindelijke transformatie in kolommen uit.
Dynamische Pivot Versie:
Als je een onbekend aantal kolommen hebt (Paul, John, Tim, Eric in je voorbeeld) en dan een onbekend aantal kleuren om te transformeren, kun je dynamic sql gebruiken om de lijst te genereren naar UNPIVOT
en dan PIVOT
:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
De dynamische versie query's zowel yourtable
en vervolgens de sys.columns
tabel om de lijst van items naar UNPIVOT
en PIVOT
te genereren. Deze wordt dan toegevoegd aan een query string die wordt uitgevoerd. Het voordeel van de dynamische versie is dat als je een veranderende lijst van kleuren
en/of namen
hebt, dit de lijst genereert tijdens run-time.
Alle drie de queries zullen hetzelfde resultaat opleveren:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Dit vereist normaal gesproken dat u ALLE kolom- EN rijlabels van tevoren kent. Zoals u in onderstaande query kunt zien, worden de labels zowel bij de UNPIVOT- als bij de (re)PIVOT-bewerking in hun geheel vermeld.
MS SQL Server 2012 Schema Setup:
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
Vraag 1:
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Gebaseerd op deze oplossing van bluefeet hier is een opgeslagen procedure die dynamische sql gebruikt om de getransponeerde tabel te genereren. Het vereist dat alle velden numeriek zijn behalve de getransponeerde kolom (de kolom die de header zal zijn in de resulterende tabel):
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
U kunt het testen met de tabel die bij dit commando wordt geleverd:
exec SQLTranspose 'yourTable', 'color'