SQLデータベースクエリ行と列の変換結果を実現するSQL文
行は列を回転し、列は私たちが開発の過程でよく出会う問題です.行転列は一般にCASE WHEN文によって実現されるが、SQL SERVER 2005に追加された演算子PIVOTによって実現される.伝統的な方法で、分かりやすいです.階層がはっきりしていて、慣れています.でもPIVOT、
UNPIVOTが提供する文法は一連の複雑なSELECTより...CASE文で指定されている構文は、より単純で読みやすいものです.以下に、いくつかの簡単な例を用いて、以下の転行、行転列の問題を紹介します.
まず、学生の成績表(以下簡略化)を通じて、下りの転列をイメージします.
CREATE
TABLE
[
StudentScores
]
(
[
UserName
]
NVARCHAR
(
20
),
--
学生の名前
[
Subject
]
NVARCHAR
(
30
),
--
科目
[
Score
]
FLOAT
,
--
成績
)
INSERT
INTO
[
StudentScores
]
SELECT
'
Nick
'
,
'
国語
'
,
80
INSERT
INTO
[
StudentScores
]
SELECT
'
Nick
'
,
'
数学
'
,
90
INSERT
INTO
[
StudentScores
]
SELECT
'
Nick
'
,
'
英語
'
,
70
INSERT
INTO
[
StudentScores
]
SELECT
'
Nick
'
,
'
せいぶつ
'
,
85
INSERT
INTO
[
StudentScores
]
SELECT
'
Kent
'
,
'
国語
'
,
80
INSERT
INTO
[
StudentScores
]
SELECT
'
Kent
'
,
'
数学
'
,
90
INSERT
INTO
[
StudentScores
]
SELECT
'
Kent
'
,
'
英語
'
,
70
INSERT
INTO
[
StudentScores
]
SELECT
'
Kent
'
,
'
せいぶつ
'
,
85
もし私がすべての学生の各科の成績を知りたいならば、その上すべての学生のすべての成績は1行に並んで、このように私が見て、統計して、データをエクスポートするのに便利です
SELECT
UserName,
MAX
(
CASE
Subject
WHEN
'
国語
'
THEN
Score
ELSE
0
END
)
AS
'
国語
'
,
MAX
(
CASE
Subject
WHEN
'
数学
'
THEN
Score
ELSE
0
END
)
AS
'
数学
'
,
MAX
(
CASE
Subject
WHEN
'
英語
'
THEN
Score
ELSE
0
END
)
AS
'
英語
'
,
MAX
(
CASE
Subject
WHEN
'
せいぶつ
'
THEN
Score
ELSE
0
END
)
AS
'
せいぶつ
'
FROM
dbo.
[
StudentScores
]
GROUP
BY
UserName
クエリーの結果は図のように、学生一人一人のすべての成績をよく知ることができます.
次に2番目の小さな列を見てみましょう.ゲームプレイヤーのチャージテーブル(説明のために挙げた小さな例)があり、
Code
[http://www.oeedu.com]
CREATE
TABLE
[
Inpours
]
(
[
ID
]
INT
IDENTITY
(
1
,
1
),
[
UserName
]
NVARCHAR
(
20
),
--
[
CreateTime
]
DATETIME
,
--
[
PayType
]
NVARCHAR
(
20
),
--
[
Money
]
DECIMAL
,
--
[
IsSuccess
]
BIT
,
--
1 ， 0
CONSTRAINT
[
PK_Inpours_ID
]
PRIMARY
KEY
(ID)
)
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-05-01
'
,
'
'
,
50
,
1
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-06-14
'
,
'
'
,
50
,
1
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-06-14
'
,
'
'
,
100
,
1
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-06-14
'
,
'
'
,
100
,
1
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-07-14
'
,
'
'
,
100
,
1
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-07-14
'
,
'
'
,
100
,
1
INSERT
INTO
Inpours
SELECT
'
'
,
'
2010-07-14
'
,
'
'
,
100
,
1
以下に統計データの需要が来て、日付、支払い方式によってチャージ金額情報を統計することを要求します.これも典型的な行転列の例です.次のスクリプトで目的を達成できます
Code
[http://www.oeedu.com]
SELECT
CONVERT
(
VARCHAR
(
10
), CreateTime,
120
)
AS
CreateTime,
CASE
PayType
WHEN
'
'
THEN
SUM
(
Money
)
ELSE
0
END
AS
'
'
,
CASE
PayType
WHEN
'
'
THEN
SUM
(
Money
)
ELSE
0
END
AS
'
'
,
CASE
PayType
WHEN
'
'
THEN
SUM
(
Money
)
ELSE
0
END
AS
'
'
,
CASE
PayType
WHEN
'
'
THEN
SUM
(
Money
)
ELSE
0
END
AS
'
'
FROM
Inpours
GROUP
BY
CreateTime, PayType
図に示すように,我々はこのような出力結果を得ただけで,所望の結果を得るにはさらに処理が必要である.
SELECT CreateTime,ISNULL(SUM([支付宝]),0)AS[支付宝],ISNULL(SUM([携帯メール]),0)AS[携帯メール],ISNULL(SUM([工商銀行カード]),0)AS[工商銀行カード],ISNULL(SUM([建設銀行カード]),0)AS[建設銀行カード]FROM(SELECT CONVERT(VARCHR(10),CreateTime,120)AS CreateTime,CASE PayType WHIN'支付宝'THEN SUM(Money)ELSE 0 END AS'支付宝',CASE PayType WHIN'携帯メール'THEN SUM(Money)ELSE 0 END AS'携帯メール',CASE PayType WHIN'工商銀行カード'THEN SUM(Money)ELSE 0 END AS'工商銀行カード',CASE PayType WHEN'建設銀行カード'THEN SUM(Money)ELSE 0 END AS'建設銀行カード'FROM Inpours GROUP BY CreateTime,PayType)TGROUPBY CreateTime
それは転列を実行して、肝心なのは論理を整理して、しかもグループ(Group by)の概念に対して比較的にはっきりしています.上の2つの列は基本的に行転列のタイプです.しかし、質問が来ました.上は私が説明するために作った簡単な列です.実際には、支払い方法が特に多く、論理も複雑で、為替レートや手数料などにかかわる可能性があります(かつてはこのようなものを作っていました)、支払い方法が特に多ければ、私たちのCASE WHENはたくさん出てきて、確かに怒っています.そして、支払い方法が追加されました.スクリプトを修正しなければなりません.上のスクリプトを動的SQLで書き換えると、私たちはこの問題を簡単に解決することができます.
Code
[http://www.oeedu.com]
DECLARE
@cmdText
VARCHAR
(
8000
);
DECLARE
@tmpSql
VARCHAR
(
8000
);
SET
@cmdText
=
'
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
'
CHAR
(
10
);
SELECT
@cmdText
=
@cmdText
'
CASE PayType WHEN
'''
PayType
'''
THEN SUM(Money) ELSE 0 END AS
'''
PayType
'''
,
'
CHAR
(
10
)
FROM
(
SELECT
DISTINCT
PayType
FROM
Inpours ) T
SET
@cmdText
=
LEFT
(
@cmdText
,
LEN
(
@cmdText
)
-
2
)
--
， CHAR(10) LEFT(@cmdText, LEN(@cmdText) -1)
SET
@cmdText
=
@cmdText
'
FROM Inpours GROUP BY CreateTime, PayType
'
;
SET
@tmpSql
=
'
SELECT CreateTime,
'
CHAR
(
10
);
SELECT
@tmpSql
=
@tmpSql
'
ISNULL(SUM(
'
PayType
'
), 0) AS
'''
PayType
'''
,
'
CHAR
(
10
)
FROM
(
SELECT
DISTINCT
PayType
FROM
Inpours ) T
SET
@tmpSql
=
LEFT
(
@tmpSql
,
LEN
(
@tmpSql
)
-
2
)
'
FROM (
'
CHAR
(
10
);
SET
@cmdText
=
@tmpSql
@cmdText
'
) T GROUP BY CreateTime
'
;
PRINT
@cmdText
EXECUTE
(
@cmdText
);
以下はPIVOTによる行転列の使い方ですが、比較してみると、確かに簡単で読みやすいです(ほほほ、習慣の前提の下で)
Code
[http://www.oeedu.com]
SELECT
CreateTime,
[
]
,
[
]
,
[
]
,
[
]
FROM
(
SELECT
CONVERT
(
VARCHAR
(
10
), CreateTime,
120
)
AS
CreateTime,PayType,
Money
FROM
Inpours
) P
PIVOT (
SUM
(
Money
)
FOR
PayType
IN
(
[
]
,
[
]
,
[
]
,
[
]
)
)
AS
T
ORDER
BY
CreateTime
このようなエラーが発生することがあります.
メッセージ325、レベル15、ステータス1、9行目
「PIVOT」付近に文法エラーがあります.この機能を有効にするには、現在のデータベースの互換性レベルをより高い値に設定する必要があります.ストレージ・プロシージャsp_についてdbcmptlevelの詳細については、ヘルプを参照してください.
これは、SQL Server 2005以降にアップグレードされたデータベースでPIVOTとUNPIVOTを使用する場合、データベースの互換性レベルを90以上に設定する必要があるためです.データベース互換性レベルの設定方法については、sp_を参照してください.dbcmptlevel (Transact-SQL). たとえば、上記のスクリプトを実行する前にEXEC sp_を付けるだけです.dbcmptlevel Test, 90; OKです.Testは所在するデータベースの名前です.
次に,列転行を見てみると,主にユニオンALL,MAXによって実現される.次のような時計があれば
コード#コード#
CREATE
TABLE
ProgrectDetail(ProgrectName
NVARCHAR
(
20
),
--
プロジェクト名
OverseaSupply
INT
,
--
海外仕入先供給数量
NativeSupply
INT
,
--
国内仕入先供給数量
SouthSupply
INT
,
--
南方サプライヤー供給数量
NorthSupply
INT
--
北方仕入先供給数量
)
INSERT
INTO
ProgrectDetail
SELECT
'
A
'
,
100
,
200
,
50
,
50
UNION
ALL
SELECT
'
B
'
,
200
,
300
,
150
,
150
UNION
ALL
SELECT
'
C
'
,
159
,
400
,
20
,
320
UNION
ALL
SELECT
'
D
'
,
250
,
30
,
15
,
15
次のスクリプトで実現できます.クエリーの結果は下図のようになります.
コード#コード#
SELECT
ProgrectName,
'
OverseaSupply
'
AS
Supplier,
MAX
(OverseaSupply)
AS
'
SupplyNum
'
FROM
ProgrectDetail
GROUP
BY
ProgrectName
UNION
ALL
SELECT
ProgrectName,
'
NativeSupply
'
AS
Supplier,
MAX
(NativeSupply)
AS
'
SupplyNum
'
FROM
ProgrectDetail
GROUP
BY
ProgrectName
UNION
ALL
SELECT
ProgrectName,
'
SouthSupply
'
AS
Supplier,
MAX
(SouthSupply)
AS
'
SupplyNum
'
FROM
ProgrectDetail
GROUP
BY
ProgrectName
UNION
ALL
SELECT
ProgrectName,
'
NorthSupply
'
AS
Supplier,
MAX
(NorthSupply)
AS
'
SupplyNum
'
FROM
ProgrectDetail
GROUP
BY
ProgrectName
UNPIVOTでは次のように実現されます.
コード#コード#
SELECT
ProgrectName,Supplier,SupplyNum
FROM
(
SELECT
ProgrectName, OverseaSupply, NativeSupply,SouthSupply, NorthSupply
FROM
ProgrectDetail)TUNPIVOT (SupplyNum
FOR
Supplier
IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )) P