How to set columns from dynamic query results


I am writing a query to calculate running totals of a few things over time. The time increments are in weekly buckets (every monday), however as time goes on, nothing needs to be in the past. This means my weekly buckets will be floating and always staying in the future. I have seen a couple links for how to setup a PIVOT function, however all of those examples the columns are fixed values. How do I get my query results to be displayed as my column names?

Here is my code which outputs what I need my column names to be. I set the increment to "1" to get just 4 results for testing, but will probably open it up to 12 months.

DECLARE @Startdate as date
DECLARE @Enddate as date

SET @Startdate = getdate()
SET @Enddate = DATEADD(Month,1,@StartDate)

;WITH cte(myDate) AS (  SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate 
                        ELSE DATEADD(Week,DATEDIFF(Week,0,@StartDate)+1,0) END AS myDate
                        UNION ALL
                        SELECT DATEADD(Week,1,myDate)
                        FROM cte
                        WHERE DATEADD(Week,1,myDate) <=  @EndDate )

SELECT CONVERT(date,myDate) AS BuildWeek
FROM cte
By : Nicho247


You will have to go dynamic.

You are missing some vital details. So I hope this will help with some guidance.

Below is a modified version of an answer from earlier today.

Declare @Startdate as date,@Enddate as date
Set @Startdate = getdate()
Set @Enddate   = DATEADD(Month,1,@StartDate)

Declare @SQL varchar(max) = ''
Declare @Col varchar(max) = '
,[Title] = sum(case when Date between ''[DateR1]'' and ''[DateR2]'' then [YourField] else null end)'

;with cte(myDate) as ( 
      Select Case When DatePart(Day,@StartDate) = 1 Then @StartDate Else DateAdd(Week,DateDiff(Week,0,@StartDate)+1,0) end as myDate
      Union All
      Select DateAdd(Week,1,myDate)
       From  cte
       Where  DateAdd(Week,1,myDate) <=  @EndDate 
Select @SQL = @SQL + Replace(Replace(Replace(@Col,'[DateR1]',DateR1),'[DateR2]',DateR2),'Title',Title)
 From (Select DateR1 = cast(myDate as Date)
             ,DateR2 = DateAdd(DAY,6,cast(myDate as Date))
             ,Title  = 'Week Of '+Substring(DateName(WEEKDAY,myDate),1,3)+' '+Substring(DateName(MONTH,myDate),1,3)+' '+cast(Day(myDate) as varchar(5))
        From  cte
       ) A 
Set @SQL = 'Select ID'[email protected]+' 
From  YourTable 
Group By ID
Print @SQL

Returns the following SQL which would be executed

Select ID
,[Week Of Mon Sep 26] = sum(case when Date between '2016-09-26' and '2016-10-02' then [YourField] else null end)
,[Week Of Mon Oct 3] = sum(case when Date between '2016-10-03' and '2016-10-09' then [YourField] else null end)
,[Week Of Mon Oct 10] = sum(case when Date between '2016-10-10' and '2016-10-16' then [YourField] else null end)
,[Week Of Mon Oct 17] = sum(case when Date between '2016-10-17' and '2016-10-23' then [YourField] else null end) 
From  YourTable 
Group By ID

string sql = "SELECT studnum,course,f_name,l_name,color_image FROM table3 WHERE [email protected]";
command = new SqlCommand(sql, conn);
command.Parameters.Add(new SqlParameter("@Name", textBoxfname.Text));

This video can help you solving your question :)
By: admin