sql : do dummy rows

Question

I have a table like this:

a1   b1
a1   b2
a2   b2
a2   b3

And I want to do a 'dummy rows'to have a result like this:

a1   b1   1
a1   b2   1
a1   b3   0
a2   b1   0
a2   b2   1
a2   b3   1

I have done a JOIN but it take too long cuz I have 1.4M rows in my table, anyone has a better idea ?

Which RDBMS (vendor and version)? Do you a need an *each-with-each and look it this pair exists*? Is there some kind of catalog table with all existing a or b values or do you take all really existing values without duplicates? — Shnugo, Jun 08 '16 at 20:01

Shnugo · Accepted Answer · 2016-06-08T20:26:14.957

This was an approach with SQL Server syntax

DECLARE @dummyTable TABLE(aValue VARCHAR(10),bValue VARCHAR(10));
INSERT INTO @dummyTable VALUES
 ('a1','b1')
,('a1','b2')
,('a2','b2')
,('a2','b3');

WITH DistinctA AS
(SELECT DISTINCT aValue FROM @dummyTable)
,DistinctB AS
(SELECT DISTINCT bValue FROM @dummyTable)
SELECT a.aValue
      ,b.bValue
      ,CASE WHEN EXISTS(SELECT 1 
                        FROM @dummyTable AS dt 
                        WHERE dt.aValue=a.aValue AND dt.bValue=b.bValue) THEN 1 ELSE 0 END AS Existing 
FROM DistinctA AS a
CROSS JOIN DistinctB AS b
ORDER BY a.aValue,b.bValue

sql : do dummy rows

1 Answers1