Advanced SQL for Data Analysis

Hey there, I’m back! 😎

Photo by Caspar Camille Rubin on Unsplash

Let me ask you something, have you ever run into this kind of table while working on a project?

id         attr1      attr2      ...        attrN     
---------- ---------- ---------- ---------- ----------
1           
2           
3           
4           
5

Well, I have, and it’s sometimes really hard to make analysis on this kind of table structure. Not blaming anybody, but this table could be modeled in a very different and more efficient way, but anyways, who am I to judge? 😏

If you needed to query this table, and for example, look if an id has a specific attribute somewhere in all those columns, you would have to do something like this:

SELECT 
  id, attr1, attr2, ... attrN
FROM 
  dbo.attributes_table
WHERE
    attr1 = 'some value'
OR  attr2 = 'some value'
OR  attr3 = 'some value'
...
OR  attrN = 'some value'

It’s very repetitive, and could lead to different problems. You could forget a column name, wrong logical operator, etc.

Also, you could use UNPIVOT, dynamic SQL or any other approach. Luckily for us, SQL has a very elegant way to do this (elegant according to me 😁) that will help us analyze our data in a more efficient way.