I need to create a report using SQL Server 2008R2 Stored procedure that will return the following shape of data:
My view in SQL Server is returning data like this:
The user will select start_date and end_date and that date range will be displayed in columns, i.e. if user selects Apr 1,2012 to Apr 7, 2012 then 7 days, Day1 to Day7 will be shown in columns. The left column will show the totals of "ApplicationId" field. Note that "Assigned", "Processing",.. are Status field.
Check this example.. as like i have implemented using this one. you can create your dynamic columns/ dynamic pivot. but you have to it using execute in sql server.
You can implement/ modify your query as you want.
Ref: Pivots with Dynamic Columns in SQL Server 2005
DECLARE @query NVARCHAR(4000) SET @query = N'SELECT tID, '+ @cols +' FROM (SELECT t2.tID
, t1.ColName
, t2.Txt FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p PIVOT ( MAX([Txt]) FOR ColName IN ( '+ @cols +' ) ) AS pvt ORDER BY tID;'
Executing this with
EXECUTE(@query)
For more reference about this:
SQL Server - Dynamic PIVOT Table - SQL Injection
Dynamic PIVOT Query For Cross Tab Result
Dynamic Pivoting in SQL Server - it contains some example that somewhat match your requirement..
Hope this help..
You can do it using a bit of DATEDIFF and PIVOT. Like this...
select name, max(DAY_1) as DAY_1, max(DAY_2) as DAY_2, max(DAY_3) as DAY_3, max(DAY_4) as DAY_4, max(DAY_5) as DAY_5 from (
SELECT s.value_Date, name, end_datetime,
'DAY_' + cast( DATEDIFF(DAY, dates.value_date, today.value_date) as varchar) as day
FROM schema1.VW_SKY_BATCH_STATUS S
CROSS JOIN (select top 1 VALUE_DATE from schema2.BUSINESS_DATE where CALENDAR = 'london' order by VALUE_DATE desc) as today
JOIN (select top 6 VALUE_DATE from schema2.BUSINESS_DATE where CALENDAR = 'london' order by VALUE_DATE desc) dates on dates.VALUE_DATE = s.VALUE_DATE
JOIN Reports.REPORT_DESCRIPTION rd on rd.ID = s.PROFILE_ID)
AS SourceTable
PIVOT (MAX(end_datetime) FOR day IN (DAY_1, DAY_2, DAY_3, DAY_4, DAY_5)) AS pt
GROUP BY name
