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
はこのシナリオには複雑すぎるように思われますが、SQL ServerのUNPIVOT
とPIVOT
関数の両方を使って非常に簡単に適用することができます。
しかし、これらの関数にアクセスできない場合は、UNPIVOT
にUNION ALL
を使用し、PIVOT
にCASE
文を使用した集約関数を使用して、このシナリオを再現することができます。
テーブルの作成:。
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, Aggregate and 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
SQL Fiddle with Demo]2を参照してください。
UNION ALLはデータの
UNPIVOTを実行して、列
Paul, John, Tim, Ericを別々の行に変換します。次に、集計関数の
sum()を
case文とともに適用して、各
color` の新しい列を取得します。
アンピボットとピボットの静的バージョン:
SQL ServerのUNPIVOT
とPIVOT
関数は、この変換を非常に簡単にしてくれます。 変換したい値がすべてわかっているのであれば、それらをハードコーディングして静的バージョンにして結果を得ることができます。
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][3]を参照してください。
UNPIVOTを使った内側のクエリは、
UNION ALLと同じ機能を果たします。このクエリは列のリストを受け取って行に変換し、
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][4]を参照してください。
ダイナミックバージョンでは、yourtable
とsys.columns
テーブルの両方に問い合わせて、UNPIVOT
とPIVOT
のアイテムリストを生成します。 これをクエリ文字列に追加して実行します。動的バージョンの利点は、colors
やnames
のリストが変更される場合に、ランタイムにリストを生成することです。
この3つのクエリはすべて同じ結果をもたらします。
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
[3]: http://sqlfiddle.com/#!3/09920/4 [4]: http://sqlfiddle.com/#!3/09920/5
このためには、通常、すべての列および行のラベルを事前に知っておく必要があります。 下のクエリを見ると、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 |
1.テーブル名が与えられれば、sys.columnsやFOR XMLでlocal-name()を使って、すべてのカラム名を決定することができます。 2.また、FOR XMLを使って、異なる色(または1つの列の値)のリストを構築することができます。 3.3. 上記を組み合わせて動的なSQLバッチにすることで、あらゆるテーブルを扱うことができます。
bluefeet]2のこの解決策に基づいて、ダイナミックSQLを使用して転置されたテーブルを生成するストアドプロシージャを以下に示します。このプロシージャでは、転置された列(生成されるテーブルのヘッダーとなる列)を除いて、すべてのフィールドが数値であることが要求されます。
/****** 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'