[解決済み] SQLで列と行を入れ替える簡単な方法？

質問

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);

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

参照 SQL Fiddle with Demo

は UNION ALL が実行されます。 UNPIVOT の列を変換することで、データの Paul, John, Tim, Eric を別々の行にする。そして、集計関数 sum() を使用して case ステートメントを使用して、新しいカラムを各 color .

UnpivotとPivot Staticバージョン。

どちらも 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

内側のクエリに UNPIVOT と同じ働きをします。 UNION ALL . これは、列のリストを受け取り、それを行に変換します。 PIVOT で、最終的に列への変換を行う。

Dynamic 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 とすると、実行時にリストが生成されます。

3つのクエリはすべて同じ結果を生成します。

| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric |   3 |     5 |    1 |
| John |   5 |     4 |    2 |
| Paul |   1 |     8 |    2 |
|  Tim |   1 |     3 |    9 |