カラム名を元にデータを整形する
CSV取り込み用テーブルを作成した場合など、
大量の列が介在することになったりする。
その場合、データの整理とか統計を行うときに弊害となる場合がある。
その弊害を解消すべく、列を分解してテーブルに書き込む例を示す。
なお、この例だと、一時テーブルを使っているので必要に応じて
ロジックを変更する方向で。
...長くなった...
前準備
たくさん列のあるデータを準備する
CREATE TABLE [dbo].[Table_1]( [code1] [int] NOT NULL , [item] [int] NOT NULL , [test1] [nchar](5) DEFAULT '00000', [test2] [nchar](5) DEFAULT '00000', [test3] [nchar](5) DEFAULT '00000', [test4] [nchar](5) DEFAULT '00000', [test5] [nchar](5) DEFAULT '00000', [test6] [nchar](5) DEFAULT '00000', [test7] [nchar](5) DEFAULT '00000', [test8] [nchar](5) DEFAULT '00000', [test9] [nchar](5) DEFAULT '00000', [test10] [nchar](5) DEFAULT '00000', [test11] [nchar](5) DEFAULT '00000', [test12] [nchar](5) DEFAULT '00000', [test13] [nchar](5) DEFAULT '00000', [test14] [nchar](5) DEFAULT '00000', [test15] [nchar](5) DEFAULT '00000', [test16] [nchar](5) DEFAULT '00000', [test17] [nchar](5) DEFAULT '00000', [test18] [nchar](5) DEFAULT '00000', [test19] [nchar](5) DEFAULT '00000', [test20] [nchar](5) DEFAULT '00000', [test21] [nchar](5) DEFAULT '00000', [test22] [nchar](5) DEFAULT '00000', [test23] [nchar](5) DEFAULT '00000', [test24] [nchar](5) DEFAULT '00000', [test25] [nchar](5) DEFAULT '00000', [test26] [nchar](5) DEFAULT '00000', [test27] [nchar](5) DEFAULT '00000', [test28] [nchar](5) DEFAULT '00000', [test29] [nchar](5) DEFAULT '00000', [test30] [nchar](5) DEFAULT '00000', [test31] [nchar](5) DEFAULT '00000', [test32] [nchar](5) DEFAULT '00000', [test33] [nchar](5) DEFAULT '00000', [test34] [nchar](5) DEFAULT '00000', [test35] [nchar](5) DEFAULT '00000', [test36] [nchar](5) DEFAULT '00000', [test37] [nchar](5) DEFAULT '00000', [test38] [nchar](5) DEFAULT '00000', [test39] [nchar](5) DEFAULT '00000', [test40] [nchar](5) DEFAULT '00000', [test41] [nchar](5) DEFAULT '00000', [test42] [nchar](5) DEFAULT '00000', [test43] [nchar](5) DEFAULT '00000', [test44] [nchar](5) DEFAULT '00000', [test45] [nchar](5) DEFAULT '00000', [test46] [nchar](5) DEFAULT '00000', [test47] [nchar](5) DEFAULT '00000', [test48] [nchar](5) DEFAULT '00000', [test49] [nchar](5) DEFAULT '00000', [test50] [nchar](5) DEFAULT '00000', [test51] [nchar](5) DEFAULT '00000', [test52] [nchar](5) DEFAULT '00000', [test53] [nchar](5) DEFAULT '00000', [test54] [nchar](5) DEFAULT '00000', [test55] [nchar](5) DEFAULT '00000', [test56] [nchar](5) DEFAULT '00000', [test57] [nchar](5) DEFAULT '00000', [test58] [nchar](5) DEFAULT '00000', [test59] [nchar](5) DEFAULT '00000', [test60] [nchar](5) DEFAULT '00000', [test61] [nchar](5) DEFAULT '00000', [test62] [nchar](5) DEFAULT '00000', [test63] [nchar](5) DEFAULT '00000', [test64] [nchar](5) DEFAULT '00000', [test65] [nchar](5) DEFAULT '00000', [test66] [nchar](5) DEFAULT '00000', [test67] [nchar](5) DEFAULT '00000', [test68] [nchar](5) DEFAULT '00000', [test69] [nchar](5) DEFAULT '00000', [test70] [nchar](5) DEFAULT '00000', [test71] [nchar](5) DEFAULT '00000', [test72] [nchar](5) DEFAULT '00000', [test73] [nchar](5) DEFAULT '00000', [test74] [nchar](5) DEFAULT '00000', [test75] [nchar](5) DEFAULT '00000', [test76] [nchar](5) DEFAULT '00000', [test77] [nchar](5) DEFAULT '00000', [test78] [nchar](5) DEFAULT '00000', [test79] [nchar](5) DEFAULT '00000', [test80] [nchar](5) DEFAULT '00000', [test81] [nchar](5) DEFAULT '00000', [test82] [nchar](5) DEFAULT '00000', [test83] [nchar](5) DEFAULT '00000', [test84] [nchar](5) DEFAULT '00000', [test85] [nchar](5) DEFAULT '00000', [test86] [nchar](5) DEFAULT '00000', [test87] [nchar](5) DEFAULT '00000', [test88] [nchar](5) DEFAULT '00000', [test89] [nchar](5) DEFAULT '00000', [test90] [nchar](5) DEFAULT '00000', [test91] [nchar](5) DEFAULT '00000', [test92] [nchar](5) DEFAULT '00000', [test93] [nchar](5) DEFAULT '00000', [test94] [nchar](5) DEFAULT '00000', [test95] [nchar](5) DEFAULT '00000', [test96] [nchar](5) DEFAULT '00000', [test97] [nchar](5) DEFAULT '00000', [test98] [nchar](5) DEFAULT '00000', [test99] [nchar](5) DEFAULT '00000', [test100] [nchar](5) DEFAULT '00000' ) ON [PRIMARY] GO
列の一覧を取得する(調査)
SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_1';
動的にカラムを読み込み、一時テーブルに落とし込む。
DECLARE @vcTargetTableName VARCHAR(50); DECLARE @vcBaseColumnName VARCHAR(50); DECLARE @vcTempTableName VARCHAR(50); DECLARE @vcBaseColumns VARCHAR(50); DECLARE @vcBaseColumnType VARCHAR(50); DECLARE @intColumnBase int; DECLARE @intColumnCurrent int; DECLARE @vcColumnName VARCHAR(50); --- 解析対象テーブル名 SET @vcTargetTableName = 'Table_1'; --- test1,test2,...のような列の場合はtestを指定 --- test%のような列が検索されます SET @vcBaseColumnName = 'test'; --- 上記カラムの定義と一致させること SET @vcBaseColumnType = ''; --- 一時テーブル名 SET @vcTempTableName = '##tempTable2'; --- 解析対象テーブルに含まれる列 SET @vcBaseColumns = 'code1,item'; --- ロジックイメージ: --- テーブルを分解して結合等使いやすい形に変更する --- 例: --- +---+----+----+ -> +---+----+----+ --- |id |dat1|dat2| -> |id | idx| dat| --- +---+----+----+ -> +---+----+----+ --- | 1| 111| 000| -> | 1| 0| 111| --- +---+----+----+ -> +---+----+----+ --- | 2| 111| 000| -> | 1| 1| 000| --- +---+----+----+ -> +---+----+----+ --- | 2| 0| 111| --- +---+----+----+ --- | 2| 1| 000| --- +---+----+----+ --- 一時テーブルを作成する --- * 何度もコールされる場合等のとき --- スピードアップさせる場合は、 --- この部分を決めうちにする --- * 決めうちにする場合の注意点 --- * @vcTempTableNameを指定した --- テーブル名で記載しないと --- エラーになってしまう。 --- * また、CREATE TABLE後、useで --- DB名を指定してください。 --- * 決めうちにした場合は、 --- 一時テーブル名の#は1つで良い EXEC('' + 'CREATE TABLE ' + @vcTempTableName + ' (' + ' code1 int' + ', item int' + ', idx int' + ', ' + @vcBaseColumnName + ' varchar(5) ' + ')' ); --- データを整形して一時テーブルに書き込む --- ベースとなるカラムIDを取得する(ループ中に引き算で使う) SELECT @intColumnBase = MIN(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @vcTargetTableName AND COLUMN_NAME LIKE @vcBaseColumnName + '%' ; --- カラム名でカーソルをまわす DECLARE curColumnName CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @vcTargetTableName AND COLUMN_NAME LIKE @vcBaseColumnName + '%' ; --- ループ開始 OPEN curColumnName; FETCH NEXT FROM curColumnName INTO @vcColumnName; WHILE @@fetch_status = 0 BEGIN ---- カーソル処理ここから --- ユニークになるようにカラムID(インデックス)を取得する SELECT @intColumnCurrent = (COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')-@intColumnBase) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @vcTargetTableName AND COLUMN_NAME = @vcColumnName; EXEC('' + 'INSERT INTO ' + @vcTempTableName + ' SELECT ' + @vcBaseColumns + ',' + @intColumnCurrent + ',' + @vcColumnName + ' FROM ' + @vcTargetTableName ); ---- カーソル処理ここまで FETCH NEXT FROM curColumnName INTO @vcColumnName; END CLOSE curColumnName; DEALLOCATE curColumnName; --- データを処理する EXEC('SELECT * FROM ' + @vcTempTableName); --- 処理が終わったらテーブルを消去する --- EXEC('' --- + 'DROP TABLE ' --- + @vcTempTableName --- + ';' --- );
