SQL Server Query To Get The Details Of The Table Where It Has Single Column And Having Multiple Rows

This shows the details of the table, where it has a single column and multiple rows. I have to output the data in such a way that the rows after each DATE valued row has to be sele

Solution 1:

There are multiple ways this could be done. As mentioned in the link referenced in the question, a CTE with partitions might work, though it seems there would need to be another column that increases in sequence that could be used for the ORDER BY clause of the ROW_NUMBER() field .

Another way is to use a cursor to process the values in the table and store the results in a temp table. Use isdate() to check if the value is a valid date. See the example below:

declare @currentRow int =0;
declare @valueCol int = 1;
declare @column_mail varchar(100)
declare @results table(date1 datetime,val1 varchar(100),val2 varchar(100),val3 varchar(100), rowNum int)
declare DbCursor cursor for 

select column_mail from mail 
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @column_mail
WHILE @@FETCH_STATUS = 0
    BEGIN
        if (isdate(@column_mail) =1)
        BEGIN 
            set @valueCol = 1;--reset
            set @currentRow = @currentRow + 1;
            insert into @results VALUES (@column_mail,null,null,null,@currentRow)
        END
        ELSE 
            BEGIN
                if (@valueCol = 1) 
                    update @results set val1 = @column_mail where rowNum = @currentRow
                if (@valueCol = 2) 
                    update @results set val2 = @column_mail where rowNum = @currentRow
                if (@valueCol = 3) 
                    update @results set val3 = @column_mail where rowNum = @currentRow
                set @valueCol = @valueCol +1;       
        END 
        FETCH NEXT FROM DBCursor
        INTO @column_mail
    END
CLOSE DBCursor
DEALLOCATE DBCURSOR

SELECT * FROM @results

Output: