CODEWITHSUNDEEP
sqlsql-server-2008
Source Matters
Source Matters

Reputation: 1221

How to output 7 records for each row, 1 for each day of week?

Trying to save myself a lot of data entry here. I have the output below currently:

CONTID  DESCR
001     Area 1
001     Area 2
001     Area 3

What I'd like to do is output 7 records for each "DESCR" (Area), 1 for each day of the week (0 - 6 accordingly).

Example output would be:

CONTID  DESCR         DAYOFWEEK
001     Area 1        0
001     Area 1        1
001     Area 1        2
001     Area 1        3
001     Area 1        4
001     Area 1        5
001     Area 1        6
001     Area 2        0
001     Area 2        1
001     Area 2        2
001     Area 2        3
001     Area 2        4
001     Area 2        5
001     Area 2        6
001     Area 3        etc...

I'd like to do this with straight SQL if possible. This is for a one-time "copy/paste" job to populate a new table.

Upvotes: 2

Views: 67

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You might do this with a CROSS JOIN

(Thx to @TT. for pointing out, that my answercan be shortened)

DECLARE @tbl TABLE(CONTID INT,  DESCR VARCHAR(100));
INSERT INTO @tbl VALUES
 (001,'Area 1')
,(001,'Area 2')
,(001,'Area 3');

SELECT *
FROM @tbl AS tbl
CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6)) AS AllDays(DayInx)

Upvotes: 3

Parfait
Parfait

Reputation: 107687

Also, consider this other solution that too uses a cross join but implicitly with derived tables for any future readers needing a generalized SQL solution in their specific RDMS:

SELECT CONTID,  DESCR, DAYOFWEEK
FROM 
  (SELECT CONTID,  DESCR FROM dataTable) AS T1,
  (SELECT 0 As DAYOFWEEK
    UNION  SELECT 1
    UNION  SELECT 2
    UNION  SELECT 3
    UNION  SELECT 4
    UNION  SELECT 5
    UNION  SELECT 6) As WeekDays

Upvotes: 1

Related Questions