I have a table with column_1, column_2, column_3, and select it by:
x
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?
Advertisement
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