SQL サーバーで行を列に変換する効率的な方法を探しています。PIVOT はあまり高速ではなく、多くのレコードを処理する必要があると聞きました。
これは私の例です:
| 識別子 | 価値 | 列名 |
|---|---|---|
| 1 | ジョン | ファーストネーム |
| 2 | 2.4 | 額 |
| 3 | ＺＨ１Ｅ４Ａ | 郵便番号 |
| 4 | フォーク | 苗字 |
| 5 | 857685 | 口座番号 |
これが私の結果です:
| ファーストネーム | 額 | 郵便番号 | 苗字 | 口座番号 |
|---|---|---|---|---|
| ジョン | 2.4 | ＺＨ１Ｅ４Ａ | フォーク | 857685 |
結果を構築するにはどうすればいいでしょうか?
ベストアンサー1
複数の行のデータを列に変換する方法はいくつかあります。
使用PIVOT
SQL Server では、PIVOT関数を使用してデータを行から列に変換できます。
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
見るデモ。
不明な数のピボットcolumnnames
columnnames転置する数が不明な場合は、動的 SQL を使用する必要があります。
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from yourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
見るデモ。
集計関数の使用
関数を使用しない場合はPIVOT、式で集計関数を使用できますCASE。
select
max(case when columnname = 'FirstName' then value end) Firstname,
max(case when columnname = 'Amount' then value end) Amount,
max(case when columnname = 'PostalCode' then value end) PostalCode,
max(case when columnname = 'LastName' then value end) LastName,
max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable
見るデモ。
複数の結合を使用する
これは複数の結合を使用して完了することもできますが、サンプル データには含まれていない各行を関連付けるための列が必要になります。ただし、基本的な構文は次のようになります。
select fn.value as FirstName,
a.value as Amount,
pc.value as PostalCode,
ln.value as LastName,
an.value as AccountNumber
from yourtable fn
left join yourtable a
on fn.somecol = a.somecol
and a.columnname = 'Amount'
left join yourtable pc
on fn.somecol = pc.somecol
and pc.columnname = 'PostalCode'
left join yourtable ln
on fn.somecol = ln.somecol
and ln.columnname = 'LastName'
left join yourtable an
on fn.somecol = an.somecol
and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'