Clever Geek Handbook

Adding non-existing data to a SQL query

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 missing values ​​here that are between 1 and 5.

Thanks in Advance

+3


source to share


2 answers


You can do it like this:



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
+2


source


First you need to generate the data you want. You can use a table of numbers for this. Use CROSS JOIN

to create all possible combinations of the two tables. Finally, the OUTER JOIN

generated data with your table.

In the next query, I used union to build a list of numbers instead of fetching them from a table. But the idea remains the 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
+3


source






More articles:


All Articles