My SQL query returns the following result (screenshot):
x y count
----------- ----------- -----------
1 1 10
1 2 2
2 4 3
2 5 5
4 1 5
5 1 8
what i want is x, y should always contain 1 to 5 values, even if the query doesn't return them, in the above scenario x is missing 3. How to add the missing values here that are between 1 & 5.
Thanks in Advance
First you need to generate the desired data. You can use a table of numbers for this. Use CROSS JOIN to generate all possible combinations of two tables. Finally, OUTER JOIN the generated data with your table.
In the following query I have used union to build a list of numbers instead of fetching them from a table. But the idea remains same:
SELECT XList.x, YList.y, #temp.count
FROM (
SELECT 1 AS x UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
) AS XList
CROSS JOIN (
SELECT 1 AS y UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
) AS YList
LEFT JOIN #temp ON XList.x = #temp.x AND YList.y = #temp.y
Result:
x y count
----------- ----------- -----------
1 1 10
2 1 NULL
3 1 NULL
4 1 5
5 1 8
1 2 2
2 2 NULL
3 2 NULL
4 2 NULL
5 2 NULL
1 3 NULL
2 3 NULL
3 3 NULL
4 3 NULL
5 3 NULL
1 4 NULL
2 4 3
3 4 NULL
4 4 NULL
5 4 NULL
1 5 NULL
2 5 5
3 5 NULL
4 5 NULL
5 5 NULL
You can do it this way:
select t1.x, t2.y, s.count from
(values(1),(2),(3),(4),(5)) t1(x) cross join
(values(1),(2),(3),(4),(5)) t2(y)
left join #temp s on t1.x = s.x and t2.y = s.y
