SQL'de sütunları satırlarla nasıl değiştirebilirim? Yer değiştirmek için basit bir komut var mı?
yani bu sonucu çevirin:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
buna:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT` bu senaryo için çok karmaşık görünmektedir.
Bu verileri dönüştürmenin birkaç yolu vardır. Orijinal gönderinizde, PIVOT
un bu senaryo için çok karmaşık göründüğünü belirtmişsiniz, ancak SQL Server'daki UNPIVOT
ve PIVOT
işlevlerinin her ikisi de kullanılarak çok kolay bir şekilde uygulanabilir.
Ancak, bu işlevlere erişiminiz yoksa, UNION ALL
ile UNPIVOT
ve ardından CASE
deyimiyle PIVOT
için bir toplama işlevi kullanılarak bu çoğaltılabilir:
Tablo Oluştur:
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);
Birlik Tümü, Toplu ve CASE Versiyonu:
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
Bakınız Demo ile SQL Fiddle
UNION ALL,
Paul, John, Tim, Ericsütunlarını ayrı satırlara dönüştürerek verilerin
UNPIVOTişlemini gerçekleştirir. Ardından, her
renkiçin yeni sütunları elde etmek üzere
casedeyimiyle
sum()` toplama işlevini uygularsınız.
Unpivot ve Pivot Statik Sürüm:
SQL server'daki UNPIVOT
ve PIVOT
fonksiyonları bu dönüşümü çok daha kolay hale getirir. Dönüştürmek istediğiniz tüm değerleri biliyorsanız, sonucu elde etmek için bunları statik bir sürüme sabit olarak kodlayabilirsiniz:
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
Bakınız Demo ile SQL Fiddle
UNPIVOTiçeren iç sorgu,
UNION ALLile aynı işlevi yerine getirir. Sütun listesini alır ve satırlara dönüştürür,
PIVOT` daha sonra sütunlara son dönüşümü gerçekleştirir.
Dinamik Pivot Versiyonu:
Bilinmeyen sayıda sütununuz varsa (örneğinizde Paul, John, Tim, Eric
) ve ardından dönüştürülecek bilinmeyen sayıda renk varsa, listeyi UNPIVOT
ve ardından PIVOT
olarak oluşturmak için dinamik sql kullanabilirsiniz:
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)
Bakınız Demo ile SQL Fiddle
Dinamik sürüm, UNPIVOT
ve PIVOT
öğelerinin listesini oluşturmak için hem yourtable
hem de sys.columns
tablosunu sorgular. Bu daha sonra çalıştırılacak bir sorgu dizesine eklenir. Dinamik versiyonun artısı, değişen bir colors
ve/veya names
listesine sahipseniz, bu listeyi çalışma zamanında oluşturacaktır.
Her üç sorgu da aynı sonucu üretecektir:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Bu normalde TÜM sütun VE satır etiketlerini önceden bilmenizi gerektirir. Aşağıdaki sorguda görebileceğiniz gibi, etiketlerin tümü hem UNPIVOT hem de (re)PIVOT işlemlerinde tamamen listelenir.
MS SQL Server 2012 Şema Kurulumu:
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;
Sorgu 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]2 tarafından sunulan bu çözüm temel alınarak, aktarılmış tabloyu oluşturmak için dinamik sql kullanan bir saklı yordam hazırlanmıştır. Dönüştürülmüş sütun (elde edilen tabloda başlık olacak sütun) hariç tüm alanların sayısal olmasını gerektirir:
/****** 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
Bu komutla birlikte verilen tablo ile test edebilirsiniz:
exec SQLTranspose 'yourTable', 'color'