SQL SELECT with an extra column with more than 1 value

I have a table with column_1, column_2, column_3, and select it by:

SELECT
    column_1,
    column_2,
    column_3
From 
    table

What I want is to add an extra column ‘hour’, it would have 24 possible values from 0 to 23. The outcome is to have every row [column_1, column_2, column_3] repeated 24 times with all possible 24 values of the extra column hour:

   column_1, column_2, column_3, hour
   'aaa',     'bbb',    'ccc',    0
   'aaa',     'bbb',    'ccc',    1
   ...

How should I do it?

Answer

you can create a temp table or view with the hour values you want. Then you can join with that new hours view or table to repeat results. Here is my snippet, I just modified that answer :

CREATE VIEW TEST.HOURS
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15  UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL 
   SELECT 18  UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL 
   SELECT 21  UNION ALL SELECT 22 UNION ALL SELECT 23;

GO

SELECT t.column1, t.column2, t.column3, i.n as hour
FROM yourTable t
JOIN TEST.HOURS i 
ON i.n between 0 and 23
order by t.column1, t.column2, t.column3, i.n