Creating a dynamic insert into assigning data to columns

Transact-SQL
1

I have this working dynamic statement that contains dynamic column heading based on "Dates" created within a temporary table. I need to do a sum on each column heading to assign the correct value for that date. Example below.

You can copy the below statement to see how it works.

image

What I would like to do is something like the following.

INSERT INTO (Part_Number, '28-Jan-18' , '29-Jan-18', '30-Jan-18')
Select Part_Number
'28-Jan-18' = 'select sum('28-Jan-18') temp with process_date = "28-Jan-18"
'29-Jan-18' = 'select sum('29-Jan-18') temp with process_date = "29-Jan-18"
'30-Jan-18' = 'select sum('30-Jan-18') temp with process_date = "30-Jan-18"
group Part_Number

--Starting statement

DECLARE @day0 datetime
DECLARE @day1 datetime
DECLARE @day2 datetime
DECLARE @day3 datetime
DECLARE @day4 datetime
DECLARE @day5 datetime
DECLARE @day6 datetime

SET @day6 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()), GETDATE())), 101)
SET @day5 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 1, GETDATE())), 101)
SET @day4 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 2, GETDATE())), 101)
SET @day3 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 3, GETDATE())), 101)
SET @day2 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 4, GETDATE())), 101)
SET @day1 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 5, GETDATE())), 101)
SET @day0 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 6, GETDATE())), 101)

DECLARE @colName varchar(50)
DECLARE @colName1 varchar(50)
DECLARE @colName2 varchar(50)
DECLARE @colName3 varchar(50)
DECLARE @colName4 varchar(50)
DECLARE @colName5 varchar(50)
DECLARE @colName6 varchar(50)

SET @colName = left(@day0, 11)
SET @colName1 = left(@day1, 11)
SET @colName2 = left(@day2, 11)
SET @colName3 = left(@day3, 11)
SET @colName4 = left(@day4, 11)
SET @colName5 = left(@day5, 11)
SET @colName6 = left(@day6, 11)

--Create temporary table with dynamic column heading and column values

CREATE TABLE #temp (Item_Code varchar(30), Facility_Code varchar(20))
DECLARE @Sql as varchar(300)
set @sql='ALTER TABLE #temp add [' + @colName + '] numeric(10,4), [' + @colName1 + '] numeric(10,4), [' + @colName2 + '] numeric(10,4), [' + @colName3 + '] numeric(10,4), [' + @colName4 + '] numeric(10,4), [' + @colName5 + '] numeric(10,4), [' + @colName6 + '] numeric(10,4)'
EXEC (@Sql)
INSERT INTO #temp VALUES ('8850', '05' , 1234.11888, 2234.11999, 3234.1999, 4234.1999, 5234.1111, 6234.1111, 7234.1111)
SELECT * FROM #temp
DROP TABLE #temp

2

I suggest creating the crosstabs (totals) code and running it using generic column names, i.e., "date1", "date2", etc..

Then, at the end of the process, rename the columns to be the specific date you want displayed as the column header. I think that will simplify the development process.

3

I should have put that in my topic. I can't use the Crosstabs (Pivot Tables). The reporting tool that I am using does not support that.

4

I don't use pivot, I mean just a standard-sql cross tab. I'm not aware of any easy way to rename / retitle columns within a reporting tool.

5

Sorry, would you happen to have an example of what you are talking about crosstabs.

6

Something like this:

SELECT Part_Number,
    SUM(CASE WHEN date_column >= @day0 AND date_column < @day1 THEN earned_hours ELSE 0 END) AS [28-Jan-18],
    SUM(CASE WHEN date_column >= @day1 AND date_column < @day2 THEN earned_hours ELSE 0 END) AS [29-Jan-18],
    SUM(CASE WHEN date_column >= @day2 AND date_column < @day3 THEN earned_hours ELSE 0 END) AS [30-Jan-18], ...
FROM dbo.table_name
GROUP BY Part_Number
ORDER BY Part_Number
7

Last question how do you change the column descriptions. The statement is by weekly. So the column names needs to change based on the date that is being passed in. That the part this is causing me problems.

8

Yeah, that's the really tough part. You almost have to use dynamic SQL one way or another. I prefer to save the results into a table with generic column names, which won't require dynamic SQL, and then rename the table columns using dynamic SQL.

For example, say you had the code above but named the columns:
day0, day1, day2, etc.

When the code got code, you could easily generate the command to rename the columns using equivalent real-day strings. So rename "day0" to "28-Jan-18", etc.. But that requires going to an intermediate table and renaming the columns before outputting the final result.

9

Would you happen to have an example?

10

No. Since you haven't provided any usable sample data -- CREATE TABLE and INSERT statements -- I don't have any way to code an example for this data.

11

Okay. I think I understand what you are saying from your previous e-mail. I am trying now. Will let you know. Thanks again for responding back.

12

Thanks for all your help Scott. I did it the way you asked me too. It worked great!!!!.

Thanks again.

BEGIN

DECLARE @day0 datetime
DECLARE @day1 datetime
DECLARE @day2 datetime
DECLARE @day3 datetime
DECLARE @day4 datetime
DECLARE @day5 datetime
DECLARE @day6 datetime

SET @day6 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()), GETDATE())), 101)
SET @day5 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 1, GETDATE())), 101)
SET @day4 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 2, GETDATE())), 101)
SET @day3 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 3, GETDATE())), 101)
SET @day2 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 4, GETDATE())), 101)
SET @day1 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 5, GETDATE())), 101)
SET @day0 = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 6, GETDATE())), 101)

DECLARE @colName varchar(50)
DECLARE @colName1 varchar(50)
DECLARE @colName2 varchar(50)
DECLARE @colName3 varchar(50)
DECLARE @colName4 varchar(50)
DECLARE @colName5 varchar(50)
DECLARE @colName6 varchar(50)

SET @colName = left(@day0, 11)
SET @colName1 = left(@day1, 11)
SET @colName2 = left(@day2, 11)
SET @colName3 = left(@day3, 11)
SET @colName4 = left(@day4, 11)
SET @colName5 = left(@day5, 11)
SET @colName6 = left(@day6, 11)

END

BEGIN

CREATE TABLE #temp
(
Item_Code varchar(30),
Item_Desc varchar(50),
Facility_Code varchar(20),
Col0 numeric(15,4),
Col1 numeric(15,4),
Col2 numeric(15,4),
Col3 numeric(15,4),
Col4 numeric(15,4),
Col5 numeric(15,4),
Col6 numeric(15,4)
)
DECLARE @Sql as varchar(300)

INSERT INTO #temp (Item_Code, Item_Desc, Facility_Code, Col0, Col1, Col2, Col3, Col4, Col5, Col6)

Select ijht.Item_Code, ijht.Item_Desc, ijht.Facility_Code,
Col0 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c0 where c0.Item_Code = ijht.Item_Code and c0.Facility_Code = ijht.Facility_Code and c0.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-0) group by c0.Actual_Process_Date),
Col1 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c1 where c1.Item_Code = ijht.Item_Code and c1.Facility_Code = ijht.Facility_Code and c1.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-1) group by c1.Actual_Process_Date),
Col2 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c2 where c2.Item_Code = ijht.Item_Code and c2.Facility_Code = ijht.Facility_Code and c2.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-2) group by c2.Actual_Process_Date),
Col3 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c3 where c3.Item_Code = ijht.Item_Code and c3.Facility_Code = ijht.Facility_Code and c3.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-3) group by c3.Actual_Process_Date),
Col4 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c4 where c4.Item_Code = ijht.Item_Code and c4.Facility_Code = ijht.Facility_Code and c4.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-4) group by c4.Actual_Process_Date),
Col5 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c5 where c5.Item_Code = ijht.Item_Code and c5.Facility_Code = ijht.Facility_Code and c5.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-5) group by c5.Actual_Process_Date),
Col6 = (Select sum(ijht.Earn_Hour) from #ItjournalEarnHourTemp as c6 where c6.Item_Code = ijht.Item_Code and c6.Facility_Code = ijht.Facility_Code and c6.Actual_Process_Date = DATEADD(week, DATEDIFF(week,0,GETDATE())-0,-6) group by c6.Actual_Process_Date)

from #ItjournalEarnHourTemp as ijht
group by ijht.Item_Code, ijht.Item_Desc, ijht.Facility_Code

BEGIN

CREATE TABLE #temp2 (Item_Code varchar(30), Item_Desc varchar(50), Facility_Code varchar(20))

set @sql='ALTER TABLE #temp2 add [' + @colName + '] numeric(10,4), [' + @colName1 + '] numeric(10,4), [' + @colName2 + '] numeric(10,4), [' + @colName3 + '] numeric(10,4), [' + @colName4 + '] numeric(10,4), [' + @colName5 + '] numeric(10,4), [' + @colName6 + '] numeric(10,4)'
EXEC (@Sql)
INSERT INTO #temp2
SELECT ii.Item_Code, ii.Item_Desc, ii.Facility_Code, ii.Col0, ii.Col1,
ii.Col2, ii.Col3, ii.Col4, ii.Col5, ii.Col6 FROM #temp as ii

select *
from #temp2

DROP TABLE #temp

END
DROP TABLE #temp2

drop tabLE #ItjournalEarnHourTemp
END