0

TableName

Sample data

Number Date_From   Date_To   
1      2020-09-29  2020-09-30

I need to return something like this:

Number Date_generated
1      2020-09-29
1      2020-09-30
GMB
  • 216,147
  • 25
  • 84
  • 135
Bo_OLK
  • 1
  • 2
  • Does this help? https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows Or try searching for the words ___sql server columns to rows___ – Abra Sep 29 '20 at 07:32
  • 2
    You're showing pivoting, not generating a range. What do you actually want? What if `Date_To` was `2020-10-30`? Would you expect 2 rows or 31? – Panagiotis Kanavos Sep 29 '20 at 07:34

3 Answers3

4

You can unpivot with a lateral join:

select t.number, x.date_generated
from mytable t
cross apply (values (t.date_from), (t.date_to)) as x(date_generated)

On the other hand, if you want to generate one row per day within the range, one option uses a recursive query:

with cte as (
    select number, date_from date_generated, date_to from mytable
    union all
    select number, dateadd(day, 1, date_generated), date_to from cte where date_generated < date_to
)
select * from cte

If you have ranges that span over more than 100 days, then you need to add option (maxrecursion 0) at the very end of the query.

GMB
  • 216,147
  • 25
  • 84
  • 135
0

Based on question already asked here :
You can use this function to split your string with the desired delimiter. in your case it's '|'

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);

Use it like it :

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], '|') as f;
pixel
  • 1
  • 4
0

you can use

select number,date_from Date_generated from tbl
union all
select number,date_to Date_generated from tbl
ali azizan
  • 311
  • 2
  • 11