creating pivot table in sql filled with 1 and 0

Question

I have table1 which contains ids [s1,s2,s3..Sn]

s1
s2
s3

I have another table2 containing indexes [0,1,2,..n].

I have third table3 [ids, start_index,stop_index]. eg:

s0 | 1 | 3 | 
s0 | 4 | 6 | 
s1 | 1 | 2 |

and so on. I want to create a pivot table which contains rows a s1,s2,s3... columns with 0,1,2,3.. content of the table should be either zero or 1.

---|0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |...
s0 |0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
s1 |0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 
.
.

You've tagged 2 very different RDBMS; which are you actually using? I've removed the conflicting tags, please retag the correct one, and only the corrrect one. What have you tried so far, and why didn't it work? — Larnu

John Cappelletti John Cappelletti · Accepted Answer · 2019-10-22T14:19:11

You can use a CROSS JOIN in concert with your index table and PIVOT the final results.

If you number of columns is variable, you would need DYNAMIC SQL

Example

 Select *
  From  (
        Select id 
              ,item = a.[index]
              ,value = case when a.[index] between b.start_index and b.stop_index then 1 else 0 end
          From  table2  A
          Cross Join table3 B 
        )  src
 Pivot (max(value) for Item in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]) ) pvt

Returns

creating pivot table in sql filled with 1 and 0

1 Answers