SELECT SUM CASE 内のCONVERT結果を列名の別名として使えますか？

Question

下の様に、前日、2日前、3日前、・・・と言うように、過去9日分を横並びに出しています。

ここで、列名に別名として振っている「前日」、「2日前」、「3日前」、・・・を「CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)」により求めた具体的な日付にするには出来るのでしょうか？

SELECT M.[ViewCD] as "銘柄コード"
      ,M.MeiName as "銘柄名"
      ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end) as "前日"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111) then T.[Tnk] else 0 end) as "2日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -3, GETDATE()), 111) then T.[Tnk] else 0 end) as "3日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -4, GETDATE()), 111) then T.[Tnk] else 0 end) as "4日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -5, GETDATE()), 111) then T.[Tnk] else 0 end) as "5日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -6, GETDATE()), 111) then T.[Tnk] else 0 end) as "6日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -7, GETDATE()), 111) then T.[Tnk] else 0 end) as "7日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -8, GETDATE()), 111) then T.[Tnk] else 0 end) as "8日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -9, GETDATE()), 111) then T.[Tnk] else 0 end) as "9日前"
  FROM [HULFT_JIP].[dbo].[tnka] as T
  inner join mega as M on M.MeiCD = T.MeiCD and SUBSTRING(M.ViewCD, 1, 1) = '8'
  where Kjn_D between CONVERT(VARCHAR, DATEADD(d, -9, GETDATE()), 111) and CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)
  group by M.[ViewCD], M.MeiName
  order by M.[ViewCD]

Microsoft SQL Server Standard (64-bit)

Windows Server 2016 Standard (10.0)

Version 14.0.20002.14

Microsoft SQL Server Management Studio to Excel 365 & 2013

Answer 1

あまりお勧めはできませんが、動的にクエリを生成することで実現はできるかと思います。

declare @sql nvarchar(1000)
declare @pram1 nvarchar(10)
set @pram1 = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)
set @sql = 'select c1 as [' + @pram1 + '] from tab1'
exec(@sql)

Answer 2

動的に列名のエイリアスを定義することは私の知る限り不可能です。エイリアスを指定する際に文字列をリテラルで囲む必要が無いことからも推測できます。
PIVOTを使うにしても、動的にin句内を指定することができません。
よって、NOBTAさんが書かれているように動的にSQLを組み立てるしかないと思います。

もしくは、一旦一時テーブルに入れて、その列名を変更してしまうかです。
これにしても一文のSQLで書くことはできません。

また、別の方法として、日付のみを先頭行に挿入するというのはいかがでしょうか？
日付のみの行を作成し、unionで結合してしまえば良いです。その際、order byで行の並びが保証されるようにした方が良いです。
例えば、以下のようなSQLです。

select 1 as 順序保証用, '銘柄コード' as 銘柄コード, '銘柄名' as 銘柄名, CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)  as 前日, CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111) as 2日前, ・・・・(以下略)

union

SELECT 2 as 順序保障用, M.[ViewCD] as "銘柄コード" ,M.MeiName as "銘柄名" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end) as "前日" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111) then T.[Tnk] else 0 end) as "2日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -3, GETDATE()), 111) then T.[Tnk] else 0 end) as "3日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -4, GETDATE()), 111) then T.[Tnk] else 0 end) as "4日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -5, GETDATE()), 111) then T.[Tnk] else 0 end) as "5日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -6, GETDATE()), 111) then T.[Tnk] else 0 end) as "6日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -7, GETDATE()), 111) then T.[Tnk] else 0 end) as "7日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -8, GETDATE()), 111) then T.[Tnk] else 0 end) as "8日前" ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -9, GETDATE()), 111) then T.[Tnk] else 0 end) as "9日前" FROM [HULFT_JIP].[dbo].[tnka] as T inner join mega as M on M.MeiCD = T.MeiCD and SUBSTRING(M.ViewCD, 1, 1) = '8' where Kjn_D between CONVERT(VARCHAR, DATEADD(d, -9, GETDATE()), 111) and CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) group by M.[ViewCD], M.MeiName order by M.[ViewCD]
order by 順序保障用

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 3

二人ともありがとうございます。今、バタバタしているので、落ち着いたら確認させていただきます。

Answer 4

trapemiyaさん

提案していただいた代替案を、3日分にしてやってみましたが、エラーになりました。

そこで、書籍「ゼロから始めるデータベース操作」を確認したところ、「■ 注意事項②―足し算の対象となるレコードの列のデータ型が一致していること」とあり、これに違反していると考えました。やり方が不味いのでしょうか？

エラー：

Msg 8114, Level 16, State 5, Line 1
データ型 varchar を numeric に変換中にエラーが発生しました。

SQL文：

select 1 as 順序保証用, '銘柄コード' as "銘柄コード", '銘柄名' as "銘柄名"
, CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)  as "前日"
, CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111)  as "2日前"
, CONVERT(VARCHAR, DATEADD(d, -3, GETDATE()), 111)  as "3日前"
  FROM internal_created.dbo.OperationDate
union
SELECT 2 as 順序保証用, M.[ViewCD] as "銘柄コード"
      ,M.MeiName as "銘柄名"
      ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end) as "前日"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111) then T.[Tnk] else 0 end) as "2日前"
	  ,sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -3, GETDATE()), 111) then T.[Tnk] else 0 end) as "3日前"
  FROM [HULFT_JIP].[dbo].[tnka] as T
  inner join HULFT_JIP.dbo.mega as M on M.MeiCD = T.MeiCD and SUBSTRING(M.ViewCD, 1, 1) = '8'
  where Kjn_D between CONVERT(VARCHAR, DATEADD(d, -9, GETDATE()), 111) and CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)
  group by M.[ViewCD], M.MeiName
  order by 順序保証用

Answer 5

HULFT_JIP.dbo.mega.ViewCD のデータ型が numeric なのではありませんか?

-- これは実行できる
select 1 as 順序保証用, N'銘柄コード' as "銘柄コード"
UNION
select 2 as 順序保証用, CAST(1234 AS nvarchar) as "銘柄コード"

-- これはエラー
-- メッセージ 8114、レベル 16、状態 5
-- Error converting data type varchar to numeric.
select 1 as 順序保証用, '銘柄コード' as "銘柄コード"
UNION
select 2 as 順序保証用, CAST(1234 AS numeric) as "銘柄コード"

Answer 6

HULFT_JIP.dbo.mega.ViewCDはchar型です。

各Select文の下記4列目以降のデータ型が、上のSelect文ではvarchar 、下はnumericと不一致で、上のvarchar型 をｓ下のnumeric型 に変換しようとして、エラーが発生していると、エラーメッセージを理解しました。

また、上下のSelect文はそれぞれ個別には問題なく実行できました。

回答ありがとうございます。

Answer 7

すみません。返信が遅れました。

各Select文の下記4列目以降のデータ型が、上のSelect文ではvarchar 、下はnumericと不一致で、上のvarchar型 をｓ下のnumeric型 に変換しようとして、エラーが発生していると、エラーメッセージを理解しました。

はい、おそらくその通りです。
検証していなかったので、すみませんでした。
format関数を使って3桁に区切ってもよいです。以下に例をあげます。検証済みです。

select 1 as 順序保証用, N'前日' as N'前日'
union
select 2 as 順序保証用, format(1234, N'#,0') as '前日'
order by 順序保証用

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 8

出力目的である単価（T.[Tnk]）出力があるため、文字から数字の変換エラーが発生しているのだと考えます。

つまり、

「CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)  as "前日"」が文字で

「sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end) as "前日"」が数字

T.[Tnk]のデータ型は「decimal(18, 8)」です。

下のSQL文も同じエラーになりました。

select 1 as 順序保証用
, CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)  as "前日"
, CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111)  as "2日前"
, CONVERT(VARCHAR, DATEADD(d, -3, GETDATE()), 111)  as "3日前"
  FROM internal_created.dbo.OperationDate
union
SELECT 2 as 順序保証用
      , sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end) as "前日"
	  , sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -2, GETDATE()), 111) then T.[Tnk] else 0 end) as "2日前"
	  , sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -3, GETDATE()), 111) then T.[Tnk] else 0 end) as "3日前"
  FROM [HULFT_JIP].[dbo].[tnka] as T
  inner join HULFT_JIP.dbo.mega as M on M.MeiCD = T.MeiCD and SUBSTRING(M.ViewCD, 1, 1) = '8'
  where Kjn_D between CONVERT(VARCHAR, DATEADD(d, -9, GETDATE()), 111) and CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)
  group by M.[ViewCD], M.MeiName
  order by 順序保証用

Msg 8114, Level 16, State 5, Line 1
データ型 varchar を numeric に変換中にエラーが発生しました。

ご説明ありがとうございました。

Answer 9

下のSQL文も同じエラーになりました。

sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end) as "前日"

上記における Kjn_D の型は不明ですが、'yyyy/mm/dd' 形式の文字列 と予想します。

sum に渡している、case 条件式 then T.[Tnk] else 0 end においては
　then T.[Tnk] は decimal(18, 8)型
　else 0 は int 型
ですよね。つまり、2 行目以降の「sum(～)」の結果は numeric 型として扱われることになります。

一方、先頭行の CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111)  as "前日" は varchar 型 となるわけですから、互換性の無い 2 つを UNION しようとして、型変換エラーになるという寸法です。

データ型 varchar を numeric に変換中にエラーが発生しました。

これを避けるため、先の回答にも書かれているように、FORMAT 関数（あるいは CONVERT 関数 または CAST 関数）を使って型を nvarchar または varchar に揃えてください。

-- "前日" 列を varchar に揃える例 --

CONVERT(VARCHAR, sum(case when Kjn_D = CONVERT(VARCHAR, DATEADD(d, -1, GETDATE()), 111) then T.[Tnk] else 0 end)) as "前日"

Answer 10

なるほど！　出来ました。ありがとうございます。

銘柄コード順に並べたかったので、上のSelect文にもViewCDを加えました。どの銘柄でも良かったので、「LEFT OUTER JOIN」を使って、適当に銘柄コードを指定しましたが、こんな感じで良いのでしょうか？

また、営業日テーブルを作成し、それを使うようにしました。

select 1 as 順序保証用, M.[ViewCD] as "銘柄コード", '銘柄名' as "銘柄名"
, CONVERT(VARCHAR, Kjn_D, 111)  as "前日"
, CONVERT(VARCHAR, Before2Days, 111)  as "2日前"
, CONVERT(VARCHAR, Before3Days, 111)  as "3日前"
, CONVERT(VARCHAR, Before4Days, 111)  as "4日前"
, CONVERT(VARCHAR, Before5Days, 111)  as "5日前"
, CONVERT(VARCHAR, Before6Days, 111)  as "6日前"
, CONVERT(VARCHAR, Before7Days, 111)  as "7日前"
  FROM internal_created.dbo.OperationDate
  left outer join HULFT_JIP.dbo.mega as M on M.ViewCD = '80074-3912-0'
union
SELECT 2 as 順序保証用, M.[ViewCD] as "銘柄コード"
      ,M.MeiName as "銘柄名"
      ,CONVERT(VARCHAR, sum(case when T.Kjn_D = D.Kjn_D then T.[Tnk] else 0 end)) as "前日"
	  ,CONVERT(VARCHAR, sum(case when T.Kjn_D = Before2Days then T.[Tnk] else 0 end)) as "2日前"
	  ,CONVERT(VARCHAR, sum(case when T.Kjn_D = Before3Days then T.[Tnk] else 0 end)) as "3日前"
	  ,CONVERT(VARCHAR, sum(case when T.Kjn_D = Before4Days then T.[Tnk] else 0 end)) as "4日前"
	  ,CONVERT(VARCHAR, sum(case when T.Kjn_D = Before5Days then T.[Tnk] else 0 end)) as "5日前"
	  ,CONVERT(VARCHAR, sum(case when T.Kjn_D = Before6Days then T.[Tnk] else 0 end)) as "6日前"
	  ,CONVERT(VARCHAR, sum(case when T.Kjn_D = Before7Days then T.[Tnk] else 0 end)) as "7日前"
  FROM [HULFT_JIP].[dbo].[tnka] as T cross join internal_created.dbo.OperationDate as D
  inner join HULFT_JIP.dbo.mega as M on M.MeiCD = T.MeiCD and SUBSTRING(M.ViewCD, 1, 1) = '8'
  where T.Kjn_D between Before7Days and D.Kjn_D
  group by M.[ViewCD], M.MeiName
  order by 順序保証用, M.[ViewCD]

Answer 11

そもそも、今回の UNION は「最初の行を列名の代わりに使う」ための代替策だったはず。
であれば先頭行は M.[ViewCD] as "銘柄コード" ではなく、'銘柄コード' as "銘柄コード" にするべきではないでしょうか。

select
  1 as "順序保証用"
, '銘柄コード' as "銘柄コード"
, '銘柄名' as "銘柄名"
, …
-- 見出し用途なのでここには FROM 句や WHERE 句を書かない。
-- もしFROM を書く場合は、単一行を返すような WHERE を併記すること。
--
union all
select
  2 as "順序保証用"
, M.[ViewCD] as "銘柄コード"
, M.MeiName as "銘柄名"
, CONVERT(VARCHAR, sum(～)) …
from …
where …
order by 1 ASC, 2 ASC;  -- UNION 結果の 1列目と2列目でソートする

Answer 12

返信ありがとうございます。

出来れば銘柄コード順に並べたいです。そして、列名を付けたいのは銘柄名よりも後ろの単価の列です。

Answer 13

ん？ それを保証するために、1 列目に "順序保証用" フィールドを用意して、2 列目（の 2 行目以降）を M.[ViewCD] as "銘柄コード" にしているのですよね。

であれば先の ORDER BY で良いと思ったのですが…何か問題が出ていますか?

Answer 14

すいません。魔界の仮面弁士さんの「order by 1 ASC, 2 ASC;  -- UNION 結果の 1列目と2列目でソートする」をちゃんと見ていませんでした。そして、その通りやったらうまく行きました。
そして、不思議なことに、「order by」を「順序保証用」のみにしても、銘柄コード順に出力されました。