Hvordan skifter jeg simpelthen kolonner med rækker i SQL? Er der en simpel kommando til at ombytte?
dvs. vende dette resultat:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
til dette:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
virker for kompleks til dette scenario.
Der er flere måder, hvorpå du kan omdanne disse data. I dit oprindelige indlæg angav du, at PIVOT
virker for kompleks til dette scenario, men det kan meget nemt anvendes ved hjælp af både UNPIVOT
og PIVOT
funktionerne i SQL Server.
Hvis du ikke har adgang til disse funktioner, kan dette dog replikeres ved hjælp af UNION ALL
til UNPIVOT
og derefter en aggregeringsfunktion med en CASE
-angivelse til 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);
Union All, Aggregat og CASE Version:
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
UNION ALL
udfører UNPIVOT
af dataene ved at omdanne kolonnerne Paul, John, Tim, Eric
til separate rækker. Derefter anvender du aggregatfunktionen sum()
med case
-erklæringen for at få de nye kolonner for hver farve
.
Unpivot og Pivot Statisk version:
Både UNPIVOT
- og PIVOT
-funktionerne i SQL Server gør denne transformation meget lettere. Hvis du kender alle de værdier, som du vil transformere, kan du hardcode dem i en statisk version for at få resultatet:
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
Den indre forespørgsel med UNPIVOT
udfører den samme funktion som UNION ALL
. Den tager listen af kolonner og omdanner den til rækker, PIVOT
udfører derefter den endelige omdannelse til kolonner.
Dynamisk Pivot-version:
Hvis du har et ukendt antal kolonner (Paul, John, Tim, Eric
i dit eksempel) og derefter et ukendt antal farver, der skal omdannes, kan du bruge dynamisk sql til at generere listen til UNPIVOT
og derefter 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)
Den dynamiske version forespørger både yourtable
og derefter tabellen sys.columns
for at generere listen over elementer til UNPIVOT
og PIVOT
. Dette tilføjes derefter til en forespørgselsstreng, der skal udføres. Fordelen ved den dynamiske version er, at hvis du har en skiftende liste over colors
og/eller names
, vil denne liste blive genereret på køretid.
Alle tre forespørgsler vil give det samme resultat:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Dette kræver normalt, at du kender ALLE kolonne- OG rækkebetegnelser på forhånd. Som du kan se i forespørgslen nedenfor, er alle etiketterne anført i deres helhed i både UNPIVOT- og (re)PIVOT-operationen.
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;
Query 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 |
Baseret på denne løsning fra bluefeet er her en lagret procedure, der bruger dynamisk sql til at generere den transponerede tabel. Den kræver, at alle felter er numeriske, undtagen den transponerede kolonne (den kolonne, der vil være overskriften i den 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
Du kan teste den med den tabel, der leveres med denne kommando:
exec SQLTranspose 'yourTable', 'color'