Kaip tiesiog perjungti stulpelius su eilutėmis SQL kalba? Ar yra kokia nors paprasta komanda, kuria galima perkelti eilutes?
t. y. paversti šį rezultatą:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
į šį:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
atrodo pernelyg sudėtingas šiam scenarijui.
Šiuos duomenis galima transformuoti keliais būdais. Pirminiame pranešime teigėte, kad PIVOT
atrodo per sudėtinga šiam scenarijui, tačiau ją galima labai lengvai pritaikyti naudojant SQL serverio funkcijas UNPIVOT
ir PIVOT
.
Tačiau, jei neturite prieigos prie šių funkcijų, tai galima atkartoti naudojant UNION ALL
į UNPIVOT
ir tada agreguojančią funkciją su CASE
teiginiu į PIVOT
:
Sukurti lentelę:
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);
Sujungti visas, agreguotas ir CASE versijas:
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
Žiūrėkite SQL Fiddle with Demo.
Funkcija UNION ALL
atlieka duomenų UNPIVOT
, transformuodama stulpelius Paul, John, Tim, Eric
į atskiras eilutes. Tada taikykite suvestinę funkciją sum()
su case
teiginiu, kad gautumėte naujus stulpelius kiekvienai spalvai
.
Anpivot ir Pivot statinė versija:
SQL serveryje esančios UNPIVOT
ir PIVOT
funkcijos labai palengvina šį pertvarkymą. Jei žinote visas reikšmes, kurias norite transformuoti, galite jas kietai įvesti į statinę versiją ir gauti rezultatą:
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
Žiūrėkite SQL Fiddle with Demo.
Vidinė užklausa su UNPIVOT
atlieka tą pačią funkciją kaip ir UNION ALL
. Ji paima stulpelių sąrašą ir paverčia jį eilutėmis, tada PIVOT
atlieka galutinį pavertimą į stulpelius.
Dinaminė Pivot versija:
Jei turite nežinomą stulpelių skaičių (jūsų pavyzdyje - Paul, John, Tim, Eric
) ir nežinomą skaičių spalvų, kurias reikia transformuoti, galite naudoti dinaminį sql, kad sukurtumėte sąrašą, kurį reikia transformuoti į UNPIVOT
, o tada į 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)
Žiūrėkite SQL Fiddle with Demo.
Dinaminė versija užklausia yourtable
ir sys.columns
lentelę, kad sukurtų UNPIVOT
ir PIVOT
elementų sąrašą. Tada jis įtraukiamas į užklausos eilutę, kuri turi būti įvykdyta. Dinaminės versijos privalumas yra tas, kad jei turite kintantį colors
ir (arba) names
sąrašą, šis sąrašas bus sugeneruotas vykdymo metu.
Visos trys užklausos duos tą patį rezultatą:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Tam paprastai reikia iš anksto žinoti VISAS stulpelių IR eilučių etiketes. Kaip matote toliau pateiktoje užklausoje, visos etiketės yra išvardytos tik UNPIVOT ir (re)PIVOT operacijose.
MS SQL Server 2012 schemos nustatymas:
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;
Užklausa 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 |
Remiantis šiuo sprendimu iš bluefeet čia yra saugoma procedūra, kuri naudoja dinaminę sql, kad sukurtų perkeltą lentelę. Ji reikalauja, kad visi laukai būtų skaitiniai, išskyrus perkeltą stulpelį (stulpelį, kuris bus gautos lentelės antraštė):
/****** 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
Ją galite išbandyti naudodami su šia komanda pateiktą lentelę:
exec SQLTranspose 'yourTable', 'color'