Multi tool use

Dynamic SQL query to return results in columns

I have this basic database table and am SQL beginner.

+-------------+--------+----------+
| location | Week | Expenses |
+-------------+--------+----------+
| Backoffice | 201851 | 80 |
| frontoffice | 201852 | 110 |
| Backoffice | 201901 | 120 |
| Backoffice | 201902 | 70 |
| frontoffice | 201903 | 68 |
+-------------+--------+----------+


Is there a way to dynamically retrieve the last 5 weeks result, instead of hard coding it every time in my pivot table?

Here is my code:

SELECT * from
(SELECT[week]
,[Expenses]
from [cafe].[dbo].[table]where location = 'Backoffice'
) as Total_Expenses pivot (sum([expenses]) for [week] in ([201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909])) as pivotable;


I would like to be able to just enter "5" and it shows me the last 5 week. May be by a "max" formula"

3 Answers
3

To handle a variable number of columns (weeks) you can use dynamic TSQL:

create table #tmp(location varchar(50), Week varchar(6),Expenses int)
insert into #tmp values
('Backoffice' ,'201851', 80),('frontoffice','201852',110),('Backoffice' ,'201901',120),('Backoffice' ,'201902', 70),('frontoffice','201903', 68)

declare @startDate date = '20190131' --Start date, probably you'll want to put Getdate() here
declare @weeks table ([weekNumber] nvarchar(4), [weekYear] nvarchar(4))
declare @weekCount int = 10 --numbers of weeks to show
declare @counter int = 0 --counter to loop on all weeks
declare @weekList varchar(max)='' --holds the list of dynamically generated column headers
declare @sql varchar(max)='' --holds the complete dynamical TSQL query

set @counter = @weekCount
while @counter > 0
begin
insert into @weeks
SELECT right('0' + cast(DATEPART(wk, DATEADD(WK,-(@counter),@startDate)) as varchar(2)),2)
, DATEPART(year, DATEADD(WK,-(@counter),@startDate))
set @counter = @counter - 1
end

select @weekList = @weekList + '[' + [weekYear] + [weekNumber] + '],' from @weeks

set @weekList = SUBSTRING(@weekList, 1, len(@weekList) - 1)

set @sql = @sql + ' SELECT * from'
set @sql = @sql + ' (SELECT[week]'
set @sql = @sql + ' ,[Expenses]'
set @sql = @sql + ' from #tmp where location = ''Backoffice'''
set @sql = @sql + ' ) as Total_Expenses pivot (sum([expenses]) for [week] in ('+@weekList+')) as pivotable;'

--execute dynamic query
exec(@sql)


Output with @weekCount = 5:

@weekCount = 5

Output with @weekCount = 10:

@weekCount = 10

If you want to select top 5 weeks (most recent weeks) use TOP: See here

TOP

SELECT TOP 5 *
FROM [cafe].[dbo].[table]
WHERE location = 'Backoffice'
ORDER BY Week Desc


Change the last line to ORDER BY Week [ ASC | DESC ] to change the order.

ORDER BY Week [ ASC | DESC ]

If you want get top 5 weeks, and sum the expense by week (I assume you want to group by week and location) then try this:

select top (5) Location, Week, sum(Expenses)
FROM table_name
WHERE location = 'backoffice'
GROUP BY Week, Location
Order By Week Desc


SQL Fiddle

P.S. I would recommend not using [table], as your table name.

You are looking for a pivot. I would suggest conditional aggregation:

SELECT SUM(CASE WHEN weeknum = 1 THEN expenses END) as week_1,
SUM(CASE WHEN weeknum = 2 THEN expenses END) as week_2,
SUM(CASE WHEN weeknum = 3 THEN expenses END) as week_3,
SUM(CASE WHEN weeknum = 4 THEN expenses END) as week_4,
SUM(CASE WHEN weeknum = 5 THEN expenses END) as week_5
FROM (SELECT t.*,
ROW_NUMBER() OVER (ORDER BY WEEK DESC) as weeknum
FROM [cafe].[dbo].[table] t
) t
WHERE location = 'Backoffice';


Note that this pivots the values but gives them generic names. If you actually want to change the names of the columns you need dynamic SQL.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.