UNION と UNION ALL

UNION と UNION ALL は、ユニオンクエリと呼ばれ、２つ以上のクエリ結果を結合して返します。
ユニオンクエリには、EXCEPT, INTERSECT 句もあります。

UNION と UNION ALL の違い

２つ以上のクエリ結果を結合して返します。
違いは、すべての結果を返すか、重複した行(レコード)は一行にして返すかの違いです。
使用するデータによっては、重複データを取り除きたい場合もあるし、すべてのレコードが必要な場合もあるので、選択して使用します。

以下に簡単な例を書きます。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)

declare @tbl2 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)

select * from @tbl1
union
select * from @tbl2
order by a_cd

select * from @tbl1
union all
select * from @tbl2
order by a_cd

UNION , UNION ALL はクエリを結合するので、ネストなどもできます。
下の例は、tbl1とtbl2を先にUNION結合し、後からtbl3をUNION ALL結合しています。
()で順序をつけることができます。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)

declare @tbl2 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)

declare @tbl3 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl3 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl3 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl3 (a_cd,b_kbn,c_kbn) values ('000000020','2',2)
insert into @tbl3 (a_cd,b_kbn,c_kbn) values ('000000030','2',2)

select * from @tbl3
union all
(
select * from @tbl1
union
select * from @tbl2
)
order by a_cd

以上、UNION と UNION ALL の簡単な使用方法でした。

NULL

SQL文を書くとき、データがnullを取りうるか否かを常に意識する必要があります。
nullを検索するためには、明示的に is null や is not null を使用する必要があります。

また、SQL Server で NULL 値を別の値にチェック後に変換するには、ISNULL 関数を使用します。

NULLについては「データベースのNULL」にも少し説明してあります。

NULL を扱う

null 値を許可している場合、かつ、その値が検索対象になっている場合は先の is null や is not null を使用します。
簡単な例を下に示しておきます。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000040','3',null)

select * from @tbl1

select * from @tbl1 where c_kbn = 0 or c_kbn <> 0 

select * from @tbl1 where c_kbn is null

select * from @tbl1 where c_kbn is not null

select a_cd, b_kbn, isnull(c_kbn, -1) from @tbl1

select a_cd, b_kbn,
    case
    when c_kbn is not null then c_kbn
    when c_kbn is null then '-1'
	end
from @tbl1

更にANSI/ISOのCOALESCE関数を使うこともできます。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000040','3',null)

select a_cd, b_kbn, coalesce(c_kbn, '-1') from @tbl1

この他、SQL Server では、ANSI_NULLS の設定で = , <> を使用できるようになる。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000040','3',null)

set ansi_nulls on

select * from @tbl1 where c_kbn = null
select * from @tbl1 where c_kbn <> null

set ansi_nulls off

select * from @tbl1 where c_kbn = null
select * from @tbl1 where c_kbn <> null

ISNULLでNullを置換する

ISNULLでNULLを他の文字に置き換える方法もありますので紹介しておきます。

構文：ISNULL(チェック対象, 変換したい文字列)
ISNULLの第1パラメータの値がNULLであれば、第2パラメータの文字列を返します（変換します）。
NULLでなければ、第1パラメータの値をそのまま返します。

declare @val integer

set @val = null
select isnull(@val, 1)

set @val = 0
select isnull(@val, 1)

以上、null の扱いかたでした。

SQLSERVER EXISTS、NOT EXISTS

EXISTS と NOT EXISTS

サブクエリの導入にキーワード EXISTS を使用した場合、そのサブクエリは「存在検査」として機能します。
NOT キーワードを付けた場合、そのサブクエリは「非存在検査」として機能します。

サブクエリの WHERE 句では、
・ データ抽出されるテーブル と 存在を試されるテーブル との リンク条件を指定します。
・ 存在を試されるテーブル の検索条件を指定します。
このことにより、このサブクエリから返される行が存在するかどうかがテストされます。
サブクエリは実際にはデータを生成せず、TRUE または FALSE の値を返します。

WHERE [NOT] EXISTS (subquery)

EXISTS演算子とは

EXISTS 演算子は、副問い合せの結果が存在するかを調べるときに使用します。
このとき、副問い合せの結果が存在するとき真になります。

NOT EXISTS演算子とは

NOT EXISTS 演算子は、副問い合せの結果が存在しないかを調べるときに使用します。
このとき、副問い合せの結果が存在しないとき真になります。

EXISTS、NOT EXISTS のテストサンプル

以下にテストSQLを載せておきます。
@tbl1には、id,kbn,pointの三項目
@tbl2には、id,kbn,priceの三項目
point,priceはランダムで数値が入ります。

@tbl1 と @tbl2は、同じid（kbnも)が入ります。
@tbl2のpriceの条件に合わせて、存在する存在しないをテストし、@tbl1のレコードを取得しています。

declare @tbl1 table(
    id int,
    kbn nchar(10) ,
    point int
)

declare @tbl2 table(
    id int,
    kbn nchar(10) ,
    price int
)

declare @start_id as int
set @start_id=0
 
while @start_id < 10
    begin
        print @start_id
        insert into @tbl1 values(@start_id, @start_id % 3, @start_id * Rand() * 10)
        insert into @tbl2 values(@start_id, @start_id % 3, @start_id * Rand() * 100)
        set @start_id = @start_id +1
    end

select * from @tbl1
select * from @tbl2

/* EXISTS */
/* テーブル@tbl2のpriceが200より大きなレコードを取得し、@tbl1の該当するレコードを表示する*/
select * from @tbl1 t1
  where EXISTS (
   select * from @tbl2 t2
     where t1.id= t2.id     --レコードのリンク
     and t2.price >= 200  --条件
　);


/* NOT EXISTS */
/* テーブル@tbl2のpriceが200より大きなレコードを取得し、@tbl1の該当しないレコードを表示する*/
select * from @tbl1 t1
  where NOT EXISTS (
    select * from @tbl2 t2
    where t1.id= t2.id     --レコードのリンク
    and t2.price >= 200  --条件
　);

IN 句と INNER JOIN 句 と比較

ちなみに、EXISTはINよりも速いとか、EXISTSは INNER JOIN より遅いだとか、良く耳にしますが、実際どうなんでしょうね？
個人的には、条件や状況によって違うのではないかと考えています。
Indexの有無、テーブルの大きさ、暗黙的な変換の有無、直前に使用テーブルにアクセスしてた等々条件や状況によって変わってくると。
とりあえず簡単なパフォーマンステストをしてみたいと思います。
サンプルSQLは以下の通りです。
自前の環境で、条件を変えてテストしてみてください。

create table #tbl1 (
    id int,
    kbn nchar(10) ,
    point int
)

create table #tbl2(
    id int,
    kbn nchar(10) ,
    price int
)
--create index #idx2 on #tbl2(price);

declare @start_id as int
set @start_id=0
 
while @start_id < 100000
    begin
        print @start_id
        insert into #tbl1 values(@start_id, @start_id % 3, @start_id * Rand() * 10)
        insert into #tbl2 values(@start_id, @start_id % 3, Rand() * 1000)
        insert into #tbl2 values(@start_id, @start_id % 3, Rand() * 1000)
        insert into #tbl2 values(@start_id, @start_id % 3, Rand() * 1000)
        set @start_id = @start_id +1
    end

--select * from #tbl1
--select * from #tbl2 where price >= 900
--ここからテスト

/* EXISTS 句でパフォーマンステスト */
print CONVERT(VARCHAR, GETDATE(), 114) 
select * from #tbl1 t1
　where EXISTS (
　　select * from #tbl2 t2
	where t1.id= t2.id     --レコードのリンク
	and t2.price >= 900
　)
print CONVERT(VARCHAR, GETDATE(), 114) 


/* IN 句でパフォーマンステスト */
print CONVERT(VARCHAR, GETDATE(), 114) 

select * from #tbl1 t1
　where t1.id IN (
　　select t2.id from #tbl2 t2
　　　where t2.price >= 900
　)

print CONVERT(VARCHAR, GETDATE(), 114) 


/* join 句でパフォーマンステスト */
print CONVERT(VARCHAR, GETDATE(), 114) 

select distinct t1.* from #tbl1 t1
inner join #tbl2 t2
on t1.id=t2.id
where t2.price >= 900

print CONVERT(VARCHAR, GETDATE(), 114) 

/* drop table */
/*
drop table #tbl1
drop table #tbl2
*/

ここでまず、INDEX無のEXISTS,IN,INNER JOINのそれぞれの実行プランを確認してみます。
EXISTS句

EXISTS句-パターン１INDEX無の実行プラン

IN句

IN句-パターン１INDEX無の実行プラン

INNER JOIN句

INNER JOIN句-パターン１INDEX無の実行プラン

実行して試してみます。
ただし一度メモリに読み込まれてしまうと、最適化された状態で動くことになるので、実際のパフォーマンスとはかなり異なります。
もし、少しでも正確に行いたいのなら、テーブルを毎回作り直して、初回で比較する方が良いと思われます。
今回はメモリに展開したうえでのパフォーマンス比較を行っています。各々2回図ります。

参考：ど初回、EXISTS句
開始：07:52:12:613
終了：07:52:14:300
結果：1687ms

—–ここから

初回：EXISTS句
開始：08:01:44:583
終了：08:01:46:287
結果：1704ms

初回：IN句
開始：07:58:20:097
終了：07:58:21:690
結果：1687ms

初回：INNER JOIN句
開始：08:07:10:097
終了：08:07:12:363
結果：2266ms

２回：EXISTS句
開始：08:08:32:600
終了：08:08:33:927
結果：1327ms

２回：IN句
開始：08:05:31:440
終了：08:05:33:160
結果：1720ms

２回：INNER JOIN句
開始：08:03:56:490
終了：08:03:57:973
結果：1483ms

なんとも締まらない結果になってしまいました。
10万件では少なかったかな。。。
とりあえず、次はpriceにIndexを貼ってやってみます。
でも実行プランも変わらなかったので、priceにIndexを貼っても効果ないようです。
どこにどう貼れば効果あるのかな・・・
ちなみに、Indexを貼るのに10秒かかりました。

初回：EXISTS句
開始：08:13:15:660
終了：08:13:17:647
結果：1987ms

初回：IN句
開始：08:15:48:677
終了：08:15:50:520
結果：1843ms

初回：INNER JOIN句
開始：08:14:47:300
終了：08:14:49:130
結果：1830ms

２回：EXISTS句
開始：08:16:37:147
終了：08:16:38:270
結果：1123ms

２回：IN句
開始：08:18:28:660
終了：08:18:29:833
結果：1173ms

２回：INNER JOIN句
開始：08:17:32:973
終了：08:17:34:647
結果：1674ms

Indexを貼っても有効に機能しなかったので結果は同じでしたね。
簡単なテーブルではパフォーマンスに差も出ないようですね。
じっくり検討すれば早くなる可能性もありますが、とりあえず簡易テストの結果、それほどの差は出ないということでした。

以上、簡単なサンプルでの説明でしたが、SQL SERVER　EXISTS、NOT EXISTS　についてでした。

SQL Server 日付の加算、減算

日付や時刻の加算や減算の行い方の備忘録です。

日付の加算、減算

SQL Serverは、DATEADD関数を使って日付型の値に日時を加算、減算する事ができます。

構文：DATEADD(element, value, target)

elementには日付要素を記述します。
日付要素は以下のようなものがあります。
year(yy or yyyy) : 年
quarter(qq or q) : 四半期
month(mm or m) : 月
dayofyear(dy or y) : 年始からの日数
day(dd, d) : 日
week(wk, w) : 週
weekday(dw) : 曜日
hour(hh) : 時
minute(mi or n) : 分
second(ss) : 秒
millisecond(ms, s) : ミリ秒

valueには加算、減算した値を設定します。
targetには、基準となる日付型の値を設定します。

日付の加算

日の加算をしたい場合は、elementに「day」、valueに「加算したい日数」、targetに基準日を設定します。

例えば、現在から5日後の日付が欲しい場合は、
DATEADD(day, 5, getdate())
で取得できます。

例えば、現在から12時間後の日付が欲しい場合は、
DATEADD(hour, 12, getdate())
で取得できます。

日付の減算

日の減算をしたい場合は、elementに「day」、valueに「減算したい日数」、targetに基準日を設定します。
減算したい日数は、マイナスで指定します。
1日前なら-1、2日前なら-2

例えば、現在から5日前の日付が欲しい場合は、
DATEADD(day, -5, getdate())
で取得できます。

例えば、現在から12時間前の日付が欲しい場合は、
DATEADD(hour, -12, getdate())
で取得できます。

上の説明でほぼ理解できたと思いますが、一応例を載せておきます。

declare @d datetime
declare @d2 datetime
declare @d3 datetime
declare @d4 datetime

print '現在時刻をセット'
set @d=getdate()
print @d
print ''

print '10時間後を指定'
set @d2=dateadd(hour,10,@d)
print @d2
print ''

print '10日時間を指定'
set @d2=dateadd(hour,-10,@d)
print @d2
print ''

print '10日後を指定'
set @d2=dateadd(day,10,@d)
print @d2
print ''

print '10日前を指定'
set @d2=dateadd(day,-10,@d)
print @d2
print ''

print '簡易な計算方法----------'
print '10日後を指定'
set @d3=@d+10
print @d3
print ''

print '10日前を指定'
set @d3=@d-10
print @d3
print ''

print '簡易な計算方法で少数点を使うと----------'
print '0.001日後を指定'
set @d4=@d+0.001
print @d4
print @d4-@d
print convert(nvarchar(36), @d4-@d,21)
declare @dec decimal(11,6)
set @dec=3600*24/1000
print @dec/60

上の例の中に簡易な計算方法も載せておきました。
基本は日単位の計算なので単純な加算、減算なら+10とか-10で計算してくれます。
また、12時間とか6時間という切りの良い時間なら、0.5や0.25と言った指定でできます。

この辺りは好みもありますが、ちゃんとDATEADDを使った方が面倒にならない気もします。
以上、SQL Server 日付の加算、減算の開発備忘録でした。

一時テーブルにPRIMARY KEYやINDEXを付ける開発備忘録です。
一時テーブルの備忘録を書いたときに、「一時テーブルには、PRIMARY KEYを付けたり、INDEXを付けたりもできます。」と書きましたので、その付け方を備忘録しておきます。

まずは、一時テーブルを作成します。

create table #tmpTable
(
	a_id int NOT NULL
	,b_cd nvarchar(2) NULL
	,c_val nvarchar(10) NULL
)

次に、PRIMARY KEYを設定します。
a_idをPRIMARY KEYとして設定します。

alter table #tmpTable ADD PRIMARY KEY CLUSTERED 
(
	a_id asc
)

もちろん、一度に作ってしまっても問題ありません。

create table #tmpTable
(
	a_id int NOT NULL
	, b_cd nvarchar(2) NULL
	, c_val nvarchar(10) NULL
	,CONSTRAINT tmpTable_PK PRIMARY KEY CLUSTERED 
    (
      a_id asc
    )
)

次に、INDEXを設定します。
もちろん、PRIMARY KEYを設定しなくてもINDEXは設定できるます。
INDEX名は適当に。
create NONCLUSTERED INDEX tmpTable_INDEX_1 ON #tmpTable
(
a_id asc
,b_cd asc
)

以上、一時テーブルにPRIMARY KEYやINDEXを付ける開発備忘録でした。

一時テーブルについての開発備忘録です。

データ処理を行っている時に、ちょっとした途中加工データを置いて置きたい場合があります。
でも、メモリに置くには大きすぎてという場合も有るし、テーブルに格納するのも排他制御や使用後の削除などの手間も有るのでテーブルは使いたくない。。。
通常のテーブルを使う場合は、固有のプロセスIDを負荷した列を設けて他のプロセスから切り離すなどをしないといけません。
そんな時に使うのが、「一時テーブル」です。一時という名前はついていますが、テーブルアクセスしてSQLで処理できるので楽にデータが扱えます。

一時テーブルは非常に使い勝手良く、ほぼ通常のテーブルと変わらぬ動きをします。
一時テーブルには、PRIMARY KEYを付けたり、Indexを付けたりもできます。
一時テーブルは、tempdb に格納される点と使用されなくなると自動的に削除されるという点を除いて、通常のテーブルと同じです。

一時テーブルには、ローカル一時テーブルとグローバル一時テーブルの2種類があります。
この2種類の一時テーブルでは、名前（#1つか2つかの違い）と表示とアクセス範囲が異なります。

ローカル一時テーブル名の先頭には、番号記号 (#) が1つ付いています。
このテーブルは、作成したユーザーの現在のセッション接続でのみ表示され、このユーザーが SQL Server のインスタンスから切断すると削除されます。
ローカル一時テーブルは、事前に現在のセッション内だけという制約も有り、最初から排他されているので排他処理等の手続きも不要です。
execSQLのように動的にSQLを動かす場合以外は、通常こちらのローカル一時テーブルを使用します。

create table #tmpTable
(
	a_id int NOT NULL
	, b_cd nvarchar(2) NULL
	, c_val nvarchar(10) NULL
)

グローバル一時テーブル名の先頭には、番号記号が2つ (##) 付いています。
このテーブルは、作成されるとすべてのユーザーに表示され、このテーブルを参照するすべてのユーザーが SQL Server のインスタンスから切断すると削除されます。
全てのユーザが使用可能になりますので、予めどのような状態のデータかがはっきりしている必要があります。
execSQLのように同一ユーザが動的にSQLを他のセッションで動かす場合のような単純な使い方であれば使い勝手も良いのですが、それ以外は通常のテーブル使用とあまり変わりません。

create table ##tmpTable
(
	a_id int NOT NULL
	, b_cd nvarchar(2) NULL
	, c_val nvarchar(10) NULL
)

しかし多くの場合、一時テーブルを使用する代わりに、table データ型の変数を使用することができます。table 変数については、変数を使用したテーブルの利用方法を見てください。

以上、一時テーブルについての開発備忘録でした。

四捨五入・切り捨て・切り上げについての備忘録。

SQL SERVERのSQLを書いているとたまに「四捨五入」・「切り捨て」・「切り上げ」を取得時にしたい時があります。
本来生データの精度は、できる限る取得できる最大限の精度で保存したいものですが、使用する時にそこまで必要ない場合などありますし。

で、四捨五入・切り捨て・切り上げに使える関数ですが、
四捨五入には 「ROUND」
切り捨てには、「CEILING」
切り上げには、「FLOOR 」を使います。

ROUNDは、以下のようにでパラメターを3つ使います。
ROUND(四捨五入したい数値, 有効桁数[,　0 – 四捨五入、0 以外- 切捨て。 オプショナルでデフォルトは 0 ])
有効桁数は(0が1の位、1が小数点第1位、2が小数点第2位、3が小数点第3位、マイナス方向は-1であれば10の位、-2であれば100の位-3であれば1,000の位のようになります。

例えば、以下のようになります。

SELECT ROUND(1234.1234, -3) --> 1000.0000
SELECT ROUND(1234.1234, -2) --> 1200.0000
SELECT ROUND(1234.1234, -1) --> 1230.0000
SELECT ROUND(1234.1234, 0) --> 1234.0000
SELECT ROUND(1234.1234, 1) --> 1234.1000
SELECT ROUND(1234.1234, 2) --> 1234.1200
SELECT ROUND(1234.1234, 3) --> 1234.1230

3つめのパラメータを設定すると

SELECT ROUND(1555.1234, -3,0) --> 2000.0000
SELECT ROUND(1555.1234, -3,1) --> 1000.0000

FLOORとCEILINGはパラメターを1つだけで、そのまま整数値に切り捨て・切り上げしてくれます。

切り捨て(FLOOR)
例えば、以下のようになります。

SELECT FLOOR(1234.1234) --> 1234
SELECT FLOOR(1234.5678) --> 1234

FLOORとCEILINGは整数値に切り捨て・切り上げするので、小数点で切り上げ切り捨てを使うには、少し工夫が必要です。
まぁ、工夫と言っても桁を合わせてあげるだけですが。
例えば、以下のようになります。

SELECT FLOOR((1234.1234 * 10)) / 10 --> 1234.100000
SELECT FLOOR((1234.5678 * 100)) / 100 --> 1234.560000

切り上げ(CEILING)

SELECT CEILING(1234.1234) --> 1235
SELECT CEILING(1234.5678) --> 1235

SELECT CEILING(1234.1234 * 10) / 10 --> 1234.200000
SELECT CEILING(1234.5678 * 100) / 100 --> 1234.570000

以上、四捨五入・切り捨て・切り上げについての開発備忘録でした。

今日はSQL SERVERのPivotに付いて使い方例を開発備忘録しておきます。

PivotはEXCELで良く集計に使われる関数です。
一般的にデータは正規化されて項目重複のない状態で保存されます。
例えば、売り上げは、日時、店舗CD、商品CD、個数、他と、店舗マスタ、商品マスターのような状態で保存されます。
人が見やすいデータは正規化されたデータと異なり、
例えば、同じ売り上げでも、日付、店舗名、商品名、個数、合計価格となります。

上の販売データの羅列を見せられても、人は売上状況を把握しづらいものです。
もっと言うなら、「森全体　⇒　森の一部　⇒　木　⇒　幹　⇒　枝　⇒　葉」のようにマクロなデータからミクロに向かう方が把握分析しやすいと言われています。

Pivotは個々の葉のレコードを寄り集めて、枝や幹、木、森の一部のような集計したデータにしてくれる関数です。

下のSQL例は、日時、商品CD、販売額のデータを

pivotー販売データテーブル

販売データpivotテーブル

declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 60)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '010', 40)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '020', 20)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '030', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 30)

insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '001', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '010', 150)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 180)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 200)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '030', 70)
 
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '001', 80)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '010', 120)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '030', 60)

select * from @tbl1

/*pivot*/
select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in ([001], [010], [020], [030])) T1 

--IN句を動的に作ろうとするとエラーになる
--in ([001], [010], [020], [030])が動的に作れない。。。
--select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (select distinct goods_cd from @tbl1)) as T 


--ので、そこで変数内にSQLを書き、実行する。
--IN句を動的に作り、@goods_listに格納する
declare @goods_list nvarchar(128)
select @goods_list=replace(REPLACE((SELECT distinct goods_cd AS [kugiri] 
                   from	@tbl1
                   for xml path ('')), '</kugiri>', '],'), '<kugiri>','[') 
set @goods_list=substring(@goods_list,0,len(@goods_list))

--実行SQLクエリ作成
--execでは、変数テーブルが読めない（アクセスできない）ため、ここにも変数テーブルを追加（通常のテーブルならこんな必要はないです）
declare @sql nvarchar(2048)
set @sql =
    '
	declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 60)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''010'', 40)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''020'', 20)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''030'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 30)

	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''001'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''010'', 150)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 180)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 200)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''030'', 70)
 
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''001'', 80)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''010'', 120)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''030'', 60)

	select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (' + @goods_list + ')) T1
	'
 
-- SQLクエリの実行
exec sp_executesql @sql;

上のデータに店舗を入れた場合は、以下のようになります。

pivotー販売データテーブル-店舗CD付き

販売データpivotテーブル店舗CD付き

declare @tbl1 table(s_date datetime, tnp_cd nvarchar(5), goods_cd nvarchar(9), price int)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '001', 60)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '001', 30)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '010', 40)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '020', 20)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '020', 20)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '030', 100)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '002', '001', 30)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '002', '001', 30)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '002', '020', 20)
 
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '001', 100)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '010', 150)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '020', 180)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '020', 200)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '030', 70)
  
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '001', 80)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '010', 120)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '030', 60)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '020', 20)
 
select * from @tbl1
 
/*pivot*/
select * from (select convert(VARCHAR, s_date, 111 ) as s_date, tnp_cd, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in ([001], [010], [020], [030])) T1 
 

以上、今日はSQL SERVERのPivotに付いて使い方例を開発備忘録でした。

SQL Server 日付の年月日だけを取り出す： CONVERT

SQL SERVERで日付の年月日だけを取り出す関数 CONVERT の開発備忘録です。

比較的よく使う変換ですので、簡単にメモ書きしておきます。
下の例は、システム日時を取得し、yy/mm/dd, yyyy/mm/dd 形式で年月日だけを取り出し、VARCHAR に変換しています。

SELECT CONVERT ( VARCHAR, GETDATE(), 11 )
SELECT CONVERT ( VARCHAR, GETDATE(), 111 )

11, 111の引数部分は以下の表を見てもらうと、11, 111なので、日本 yy/mm/dd で取得できます。
111の場合は、年が4桁表示の yyyy/mm/dd になります。
11か111以外はほぼ使わないと思いますが、たまに使うことも有るかもしれないので、一式載せておきます。

以上、SQL SERVERで日付の年月日だけを取り出す（convert）開発備忘録でした。