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このシナリオには複雑すぎるようです。
ベストアンサー1
このデータを変換する方法はいくつかあります。元の投稿では、PIVOTこのシナリオでは複雑すぎると述べていましたが、UNPIVOTそしてPIVOTSQL Server の関数。
ただし、これらの関数にアクセスできない場合は、 to を使用UNION ALLしてから、ステートメント toUNPIVOTを含む集計関数を使用してこれを複製できます。CASEPIVOT
テーブルを作成:
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、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
は、列を個別の行に変換することでデータの をUNION ALL実行します。次に、ステートメントで集計関数を適用して、各 の新しい列を取得します。UNPIVOTPaul, John, Tim, Ericsum()casecolor
アンピボットとピボットの静的バージョン:
UNPIVOTSQL Server の 関数と関数の両方を使用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
を使用した内部クエリは、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)
動的バージョンは、 と の両方をクエリしyourtableてからテーブルをクエリして、とsys.columnsのアイテムのリストを生成します。これは、実行されるクエリ文字列に追加されます。動的バージョンの利点は、 と または のリストが変化する場合に、実行時にリストが生成されることです。UNPIVOTPIVOTcolorsnames
3 つのクエリはすべて同じ結果を生成します。
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |