Sql-server – Aggregating date in one column

aggregatedatesql server

For a purpose of reporting I need to present data from table:
table A (column1, column2, date1, date2, date3,…,dateN)

My query need to present all dates in one column separated with # .

(YYYY-MM-DD# YYYY-MM-DD#..)

But problem is that number of date columns is not fixed, because from product to product can be different number of dates.

Any idea ?

Best Answer

I'm was thinking about some ugly query such as:

select isnull(convert(varchar(8), date1, 121),'') 
        + isnull('#'+convert(varchar(8), date2, 121),'')
        + isnull('#'+convert(varchar(8), date3, 121),'')
        ...
        + isnull('#'+convert(varchar(8), dateN, 121),'')
from A

But we could do better using something like:

select cast((
       select (
              select '#'+T2.N.value('./text()[1]',  'varchar(max)')
              from (
                   select T.*
                   for xml path(''), type
                   ) as T1(N)
                cross apply T1.N.nodes('/*') as T2(N)
              for xml path(''), type
              ).value('substring(./text()[1], 2)',  'varchar(max)')
       for xml path(''), type
       ) as varchar(max))
from A as T

Related Solutions

Sql-server – Filling in date holes in grouped-by-date SQL data

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE.

/* Date Range CTE */
-- Updated based on @AaronBertrand's articles linked in the comments 
-- Basically ends up being the same query as the last half of @AaronBertrand's post
declare @FromDate date;
declare @ThruDate date;
set @FromDate='2013-01-01';
set @ThruDate='2015-01-01';

with cal as (
  select top (1+datediff(day, @FromDate, @ThruDate))
      DateValue = dateadd(day, v.number, @FromDate)
    from master.dbo.spt_values v
    where v.type = 'P'
      and v.number >= 0
    order by v.number
  )
select EventDate = cal.DateValue
     , TotalEventCount = isnull(sum(EventCount), 0)
  from cal
    left join dbo.EventRecords e on cal.DateValue=e.EventDate
  group by cal.DateValue
  order by cal.DateValue

Some links about calendar tables:

Sql 2005 Calendar Tables - Why You Need One - David Stein
Sql 2008+ Creating a Date Table/Dimension on SQL 2008 - David Stein
Calendar Tables in T-SQL

Sql-server – How to generate list of dates — ie every day for the past 2 years

Using pure T-SQL, you can do the following:

;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();

Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:

SELECT [DatetimeVal]
FROM   [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');

If you want the ending date to be in the future, just replace the GETDATE() in either query.

Best Answer

Related Solutions

Sql-server – Filling in date holes in grouped-by-date SQL data

Sql-server – How to generate list of dates — ie every day for the past 2 years

Related Question