Πώς μπορώ απλά να αλλάξω στήλες με γραμμές στην SQL; Υπάρχει κάποια απλή εντολή για την αντιμετάθεση;
Δηλαδή να μετατρέψω αυτό το αποτέλεσμα:
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
φαίνεται πολύ περίπλοκο για αυτό το σενάριο.
Υπάρχουν διάφοροι τρόποι με τους οποίους μπορείτε να μετασχηματίσετε αυτά τα δεδομένα. Στην αρχική σας δημοσίευση, δηλώσατε ότι η PIVOT
φαίνεται πολύ περίπλοκη για αυτό το σενάριο, αλλά μπορεί να εφαρμοστεί πολύ εύκολα με τη χρήση των συναρτήσεων UNPIVOT
και PIVOT
στον SQL Server.
Ωστόσο, εάν δεν έχετε πρόσβαση σε αυτές τις συναρτήσεις, αυτό μπορεί να αναπαραχθεί χρησιμοποιώντας την UNION ALL
στην UNPIVOT
και στη συνέχεια μια συνάρτηση συνάθροισης με μια δήλωση CASE
στην PIVOT
:
Δημιουργία πίνακα:
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);
Ένωση όλων, συγκεντρωτική και έκδοση 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
Βλέπε SQL Fiddle with Demo
Η UNION ALL
εκτελεί την UNPIVOT
των δεδομένων μετατρέποντας τις στήλες Paul, John, Tim, Eric
σε ξεχωριστές γραμμές. Στη συνέχεια, εφαρμόζετε τη συνάρτηση αθροίσματος sum()
με τη δήλωση case
για να λάβετε τις νέες στήλες για κάθε color
.
Αποστροφή και στατική έκδοση περιστροφής:
Τόσο οι συναρτήσεις UNPIVOT
όσο και PIVOT
στον SQL server κάνουν αυτόν τον μετασχηματισμό πολύ πιο εύκολο. Εάν γνωρίζετε όλες τις τιμές που θέλετε να μετασχηματίσετε, μπορείτε να τις κωδικοποιήσετε σκληρά σε μια στατική έκδοση για να λάβετε το αποτέλεσμα:
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
Βλέπε SQL Fiddle with Demo
Το εσωτερικό ερώτημα με το UNPIVOT
εκτελεί την ίδια λειτουργία με το UNION ALL
. Παίρνει τη λίστα των στηλών και τη μετατρέπει σε γραμμές, το PIVOT
εκτελεί στη συνέχεια τον τελικό μετασχηματισμό σε στήλες.
Δυναμική έκδοση Pivot:
Εάν έχετε έναν άγνωστο αριθμό στηλών (Paul, John, Tim, Eric
στο παράδειγμά σας) και στη συνέχεια έναν άγνωστο αριθμό χρωμάτων για μετασχηματισμό μπορείτε να χρησιμοποιήσετε τη δυναμική sql για να δημιουργήσετε τη λίστα στο UNPIVOT
και στη συνέχεια στο 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)
Βλέπε SQL Fiddle with Demo
Η δυναμική έκδοση πραγματοποιεί ερωτήματα τόσο στον πίνακα yourtable
όσο και στον πίνακα sys.columns
για να δημιουργήσει τη λίστα των στοιχείων για UNPIVOT
και PIVOT
. Αυτό στη συνέχεια προστίθεται σε μια συμβολοσειρά ερωτήματος προς εκτέλεση. Το πλεονέκτημα της δυναμικής έκδοσης είναι ότι αν έχετε έναν μεταβαλλόμενο κατάλογο των colors
και/ή names
αυτό θα δημιουργήσει τον κατάλογο κατά την εκτέλεση.
Και τα τρία ερωτήματα θα παράγουν το ίδιο αποτέλεσμα:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Αυτό συνήθως απαιτεί να γνωρίζετε εκ των προτέρων ΟΛΕΣ τις ετικέτες των στηλών ΚΑΙ των γραμμών. Όπως μπορείτε να δείτε στο παρακάτω ερώτημα, οι ετικέτες παρατίθενται όλες εξ ολοκλήρου τόσο στις πράξεις UNPIVOT όσο και στις πράξεις (re)PIVOT.
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;
Ερώτηση 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 |
Με βάση αυτή τη λύση από την bluefeet, ακολουθεί μια αποθηκευμένη διαδικασία που χρησιμοποιεί δυναμική sql για να δημιουργήσει τον πίνακα που μετατίθεται. Απαιτεί όλα τα πεδία να είναι αριθμητικά εκτός από τη στήλη transposed (η στήλη που θα είναι η επικεφαλίδα στον πίνακα που θα προκύψει):
/****** 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
Μπορείτε να το δοκιμάσετε με τον πίνακα που παρέχεται με αυτή την εντολή:
exec SQLTranspose 'yourTable', 'color'