SQL サンプル
テーブル作成
テーブル作成
月別テーブル
USE [TESTDB]GO
/****** Object: Table [dbo].[Table_1] Script Date: 2023/03/30 16:04:45 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[Table_1]( [NAME] [nchar](10) NULL, [DATE] [nchar](10) NULL, [VALUE] [numeric](18, 0) NULL) ON [PRIMARY]GO
/****** Object: Table [dbo].[Table_1] Script Date: 2023/03/30 16:04:45 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[Table_1]( [NAME] [nchar](10) NULL, [DATE] [nchar](10) NULL, [VALUE] [numeric](18, 0) NULL) ON [PRIMARY]GO
キーテーブル
USE [TESTDB]GO
/****** Object: Table [dbo].[Table_1] Script Date: 2023/03/30 16:07:03 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[Table_1]( [NAME] [nchar](10) NULL, [DATE] [nchar](10) NULL, [VALUE] [numeric](18, 0) NULL) ON [PRIMARY]GO
/****** Object: Table [dbo].[Table_1] Script Date: 2023/03/30 16:07:03 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[Table_1]( [NAME] [nchar](10) NULL, [DATE] [nchar](10) NULL, [VALUE] [numeric](18, 0) NULL) ON [PRIMARY]GO
ストアドプロシージャ
ストアドプロシージャ
月別データ集計
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'SELECT T2.NAME,T202301.VALUE AS ''202301'',T202302.VALUE AS ''202302'',T202303.VALUE AS ''202303'' 'SET @Sql = @Sql + N'FROM [TESTDB].[dbo].[Table_2] AS T2 'SET @Sql = @Sql + N'LEFT JOIN (SELECT * FROM [TESTDB].[dbo].[Table_1] WHERE DATE = ''202301'') AS T202301 ON T2.NAME = T202301.NAME 'SET @Sql = @Sql + N'LEFT JOIN (SELECT * FROM [TESTDB].[dbo].[Table_1] WHERE DATE = ''202302'') AS T202302 ON T2.NAME = T202302.NAME 'SET @Sql = @Sql + N'LEFT JOIN (SELECT * FROM [TESTDB].[dbo].[Table_1] WHERE DATE = ''202303'') AS T202303 ON T2.NAME = T202303.NAME '
EXEC sp_executesql @Sql
SET @Sql = N'SELECT T2.NAME,T202301.VALUE AS ''202301'',T202302.VALUE AS ''202302'',T202303.VALUE AS ''202303'' 'SET @Sql = @Sql + N'FROM [TESTDB].[dbo].[Table_2] AS T2 'SET @Sql = @Sql + N'LEFT JOIN (SELECT * FROM [TESTDB].[dbo].[Table_1] WHERE DATE = ''202301'') AS T202301 ON T2.NAME = T202301.NAME 'SET @Sql = @Sql + N'LEFT JOIN (SELECT * FROM [TESTDB].[dbo].[Table_1] WHERE DATE = ''202302'') AS T202302 ON T2.NAME = T202302.NAME 'SET @Sql = @Sql + N'LEFT JOIN (SELECT * FROM [TESTDB].[dbo].[Table_1] WHERE DATE = ''202303'') AS T202303 ON T2.NAME = T202303.NAME '
EXEC sp_executesql @Sql