StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try this:</p> <pre><code>DECLARE @YourTable table (CUSTOMER char(1), ACTIVITIES int, WEEKNUMBER int) INSERT @YourTable VALUES ('A' , 4 , 1) INSERT @YourTable VALUES ('A' , 2 , 2) INSERT @YourTable VALUES ('A' , 0 , 3) INSERT @YourTable VALUES ('A' , 0 , 4) INSERT @YourTable VALUES ('A' , 1 , 5) INSERT @YourTable VALUES ('B' , 5 , 3) INSERT @YourTable VALUES ('C' , 2 , 4) DECLARE @StartNumber int ,@EndNumber int SELECT @StartNumber=1 ,@EndNumber=5 ;WITH AllNumbers AS ( SELECT @StartNumber AS Number UNION ALL SELECT Number+1 FROM AllNumbers WHERE Number<@EndNumber ) , AllCustomers AS ( SELECT DISTINCT CUSTOMER FROM @YourTable ) SELECT n.Number AS WEEKNUMBER, c.CUSTOMER, CASE WHEN y.Customer IS NULL THEN 0 ELSE y.ACTIVITIES END AS ACTIVITIES FROM AllNumbers n CROSS JOIN AllCustomers c LEFT OUTER JOIN @YourTable y ON n.Number=y.WEEKNUMBER AND c.CUSTOMER=y.CUSTOMER --OPTION (MAXRECURSION 500) </code></pre> <p>OUTPUT:</p> <pre><code>WEEKNUMBER CUSTOMER ACTIVITIES ----------- -------- ----------- 1 A 4 1 B 0 1 C 0 2 A 2 2 B 0 2 C 0 3 A 0 3 B 5 3 C 0 4 A 0 4 B 0 4 C 2 5 A 1 5 B 0 5 C 0 (15 row(s) affected) </code></pre> <p>I use a CTE to build a Numbers table, but you could build a permanent one look at this question: <a href="https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table">What is the best way to create and populate a numbers table?</a>. You could Write the Query without a CTE (same results as above):</p> <pre><code>SELECT n.Number AS WEEKNUMBER, c.CUSTOMER, CASE WHEN y.Customer IS NULL THEN 0 ELSE y.ACTIVITIES END AS ACTIVITIES FROM Numbers n CROSS JOIN (SELECT DISTINCT CUSTOMER FROM @YourTable ) c LEFT OUTER JOIN @YourTable y ON n.Number=y.WEEKNUMBER AND c.CUSTOMER=y.CUSTOMER WHERE n.Number>=1 AND n.Number<=5 ORDER BY n.Number,c.CUSTOMER </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload