How to Expand Out a Row into Multiple Row Result Set

How do I expand comma separated values into separate rows using SQL Server 2005?

I arrived this question 10 years after the post.
SQL server 2016 added STRING_SPLIT function.
By using that, this can be written as below.

declare @product table
(
    ProductId int,
    Color     varchar(max)
);
insert into @product values (1, 'red, blue, green');
insert into @product values (2, null);
insert into @product values (3, 'purple, green');

select
    p.ProductId as ProductId,
    ltrim(split_table.value) as Color
from @product p
outer apply string_split(p.Color, ',') as split_table;

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

Split single row into multiple rows based on Date and time in SQL Server

Below Query will help you.

CREATE TABLE #test
(
    Notifications varchar(50)
    ,StartDate datetime
    ,EndDate Datetime
    ,Id int
)

INSERT into #test
select              '001003741915','2018-08-20 07:27:00.000','2018-08-21 16:23:00.000',1
UNION select        '001003779670','2018-08-21 03:36:00.000','2018-08-21 04:36:00.000',2
UNION select        '001003779830','2018-08-21 04:36:00.000','2018-08-21 21:35:00.000',3
UNION select        '001003779835','2018-08-21 04:36:00.000','2018-08-24 21:35:00.000',4

;with cte
As (  SELECT 
        ID,Notifications,StartDate,dateadd(d, datediff(d, 1, StartDate+1), '06:00') as StartOfDay, EndDate,dateadd(d, datediff(d, 1, EndDate+1), '06:00')  as EndDayOfDate
    FROM #test
)
, Result
AS (

    select Id
            ,Notifications
            ,StartDate 
            ,CASE WHEN StartOfDay BETWEEN StartDate AND EndDate THEN  StartOfDay
                  WHEN ENDDate <StartOfDay THEN ENDDate
                  WHEN ENDDate <EndDayOfDate THEN ENDDate
                ELSE  EndDayOfDate  END AS  EndDate 
    from cte

    union ALL
    Select T.Id
            ,T.Notifications
            ,R.EndDate As StartDate
            ,CASE WHEN R.EndDate+1 < T.EndDate THEN R.EndDate+1 ELSE  T.EndDate   END AS EndDate 
    from cte  T
    INNER JOIN Result R
    ON R.Notifications=T.Notifications
    WHERE  R.EndDate <T.EndDate

)

SELECT * FROM Result order by id

Impala: Split single row into multiple rows based on Date and time

This would be a good spot for a recursive CTE, but unfortunatly Hive does not support those. Here is another aproach, that uses a derived table of numbers to split the periods:

select
    t.SrNo,
    t.Employee,
    greatest(t.startDate, date_add(to_date(t.startDate), x.n)) startDate,
    least(t.endDate, date_add(to_date(t.startDate), x.n + 1)) endDate
from mytable t
inner join (select 0 n union all select 1 union all select 2) x
    on date_add(to_date(t.startDate), x.n) <= t.endDate

You can expand the subquery to handle more possible periods per row.

Also note that this generates half-open intervals, where the end of the preceding interval is equal to the start of the next one (while in your resultset there is a one minute lag). The logic is that the interval is inclusive on its smaller bound and exclusive on the the outer bound (that way, you make sure to not leave any gap).

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

Related Topics