I have a single table looking like this:
Table extract
Owner | Attribute | value
----------------------------------------------------
10 | COLOR | BLUE
10 | COLOR | RED
10 | COLOR | GREEN
10 | SIZE | BIG
20 | COLOR | GREEN
20 | SIZE | MEDIUM
20 | MEMORY | 16G
20 | MEMORY | 32G
30 | COLOR | RED
30 | COLOR | BLUE
30 | MEMORY | 64G
Is there a SQL which will calculate a combination of all attribute with a single index (last column in the result):
Owner | Attribute | Value | Rule_No
10 | COLOR | BLUE | 1
10 | SIZE | BIG | 1
10 | COLOR | RED | 2
10 | SIZE | BIG | 2
10 | COLOR | GREEN | 3
10 | SIZE | BIG | 3
20 | COLOR | GREEN | 1
20 | SIZE | MEDIUM| 1
20 | MEMORY | 16G | 1
20 | COLOR | GREEN | 2
20 | SIZE | MEDIUM| 2
20 | MEMORY | 32G | 2
30 | COLOR | BLUE | 1
30 | MEMORY | 64G | 1
30 | COLOR | RED | 2
30 | MEMORY | 64G | 2
The rule number would be unique per owner (rule '1' for owner '10' is not related to rule '1' for owner '20'.
I tried to use the SQL cross join, but the number of attributes is not fixed, then I cannot use it (one cross join per attribute is needed) and I want the combination to be new rows instead new columns.
I am trying to use Talend Open Studio - Data Integration
to do it but a solution using only SQL would be better for me.
Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).
The simplest way to combine two tables together is using the keywords UNION or UNION ALL. These two methods pile one lot of selected data on top of the other. The difference between the two keywords is that UNION only takes distinct values, but UNION ALL keeps all of the values selected.
No, MSSQL doesn't allow such queries. You should use col LIKE '...' OR col LIKE '...' etc.
Do you really want the data in the form given in your question (which would then require further aggregation on Rule_No
to be useful in most likely situations), or are you ultimately seeking to pivot it? That is, rules are joined together (with each attribute becoming its own column) as follows:
+---------+-------+-------+--------+--------+ | Rule_No | Owner | COLOR | SIZE | MEMORY | +---------+-------+-------+--------+--------+ | 1 | 10 | BLUE | BIG | NULL | | 2 | 10 | RED | BIG | NULL | | 3 | 10 | GREEN | BIG | NULL | | 1 | 20 | GREEN | MEDIUM | 16G | | 2 | 20 | GREEN | MEDIUM | 32G | | 1 | 30 | RED | NULL | 64G | | 2 | 30 | BLUE | NULL | 64G | +---------+-------+-------+--------+--------+
One can pivot such data with a query as follows:
SELECT @t:=IF(Owner=@o,@t,0)+1 AS Rule_No,
@o:=Owner AS Owner,
`COLOR`,`SIZE`,`MEMORY`
FROM (SELECT DISTINCT Owner, @t:=0 FROM my_table) t0
LEFT JOIN (
SELECT Owner, value AS `COLOR`
FROM my_table
WHERE Attribute='COLOR'
) AS `t_COLOR` USING (Owner)
LEFT JOIN (
SELECT Owner, value AS `SIZE`
FROM my_table
WHERE Attribute='SIZE'
) AS `t_SIZE` USING (Owner)
LEFT JOIN (
SELECT Owner, value AS `MEMORY`
FROM my_table
WHERE Attribute='MEMORY'
) AS `t_MEMORY` USING (Owner)
ORDER BY Owner, Rule_No
As the attribute list is dynamic, one can use a query to construct the above SQL from which one prepares and executes a statement:
SELECT CONCAT('
SELECT @t:=IF(Owner=@o,@t,0)+1 AS Rule_No,
@o:=Owner AS Owner,
', GROUP_CONCAT(DISTINCT CONCAT(
'`',REPLACE(Attribute,'`','``'),'`'
)), '
FROM (SELECT DISTINCT Owner, @t:=0 FROM my_table) t0
', GROUP_CONCAT(DISTINCT CONCAT('
LEFT JOIN (
SELECT Owner, value AS `',REPLACE(Attribute,'`','``'),'`
FROM my_table
WHERE Attribute=',QUOTE(Attribute),'
) AS `t_',REPLACE(Attribute,'`','``'),'` USING (Owner)
') SEPARATOR ''), '
ORDER BY Owner, Rule_No
') INTO @sql
FROM my_table;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See it on sqlfiddle.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With