CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
user2331670
user2331670

Reputation: 335

Return a column per date in a range

I am using below SQL query:

select  brn,
        [time],
        sum(value) as [value]
from dbo.sales 
where date = '2014-12-31 00:00:00'     
    and brn = 1 
    and [time]!='ZZZZ'
group by brn, [time]
order by brn, [time]

And result I get is:

brn time    value
1   0800    0.00
1   0900    52.79
1   1000    5.73
1   1100    9.63
1   1200    200.08

Now I want the results for several dates (for one year-from 31-12-2014 to 31-12-2015),

For example:

brn time    31/12/2014  01/01/2015  02/01/2015  03/01/2015
1   800     5.73        5.73        5.73        5.73
1   900     52.79       52.79       52.79       52.79
1   1000    5.73        5.73        5.73        5.73
1   1100    9.63        9.63        9.63        9.63
1   1200    200.08      200.08      200.08      200.08

Upvotes: 2

Views: 3069

Answers (3)

Eralper
Eralper

Reputation: 6612

For column names build for dates, you can create a SQL dates table Then using dynamic SQL, you can use these as select fields Perhaps you can check dynamic pivot sql query for how to concatenate field values from dates table into pivot query fields

Here is the SQL Script

DECLARE @dates nvarchar(max)
SELECT @dates =
 STUFF(
 (
    SELECT ', value as [' + convert(varchar(30), cast([date] as date), 112) + ']'
    FROM [dbo].[DateTable]('20150101', '20151231')
    for xml path('')
 ),
 1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'select brn, time, value, ' + @dates + ' from mydata'
exec sp_executesql @SQL

Output is as follows

enter image description here

You can format the string representation of date values as you can see in SQL Format Date article. Instead of parameter 112, you can use 103 for dd/mm/yyyy for example

I hope it helps for solution

Upvotes: 0

gofr1
gofr1

Reputation: 15987

You can use dynamic SQL with pivoting:

select  brn,
        [time],
        sum(value) as [value],
        [date]
INTO #temp
from dbo.sales 
where brn = 1 and [time]!='ZZZZ'
     AND [date] between '2014-12-31' and '2015-12-31'
group by brn, [time], [date]


DECLARE @sql nvarchar(max),
        @col nvarchar(max)

SELECT @col = (
    SELECT DISTINCT ','+QUOTENAME([date])
    FROM #temp
    FOR XML PATH ('')
)

SELECT @sql = N'
SELECT *
FROM #temp
PIVOT (
    MAX([value]) FOR [date] IN ('+STUFF(@col,1,1,'')+')
) as pvt)'

EXEC sp_executesql @sql

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You can use conditional aggregation:

select brn, [time],
       sum(case when date = '2014-12-31' then value else 0 end)) as value_20141231,
       sum(case when date = '2015-01-01' then value else 0 end)) as value_20150101,
       . . .
from dbo.sales 
where brn = 1  and 
     [time] <> 'ZZZZ'
group by brn, [time]
order by brn, [time];

You can also restrict the dates in the where, using and date in ('2014-12-31', '2015-01-01', . . .).

Upvotes: 1

Related Questions