Transpose column to rows

Question

Hi,

Without using a cursor (if poss) I would like to turn the following data into rows based based upon the integer in column two.

So I would end up with 5 rows for the first date, 1 for the second etc.

Can anyone advise on how best to do this pls? Here is the sandbox code ...

DECLARE @Visitors TABLE (TheDate DATE, CountOfVisitors INT);

INSERT @Visitors (TheDate, CountOfVisitors) VALUES(GETDATE()-1, 5 )

INSERT @Visitors (TheDate, CountOfVisitors) VALUES(GETDATE()-2, 1 )

INSERT @Visitors (TheDate, CountOfVisitors) VALUES(GETDATE()-3, 7 );

SELECT * FROM @Visitors;

Answer 1

it depends on the version of sqlserver you are using. if using sqlserver 2022 you can cross apply to generate_series.

otherwise you need a series table. you need to know the max visitor count, then insert a row for 1 to max.

create table Series (number int not null unique)
declare @max int = 10000;
declare @i int = 0;
while (@i < @max) begin
   set @i = @i + 1
   insert Series values(@i)
end

then just join to your series table:

select TheDate
from @Visitors 
join Series on Series.Number between 1 and CountOfVisitors