Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql combination from and to a single table

Tags:

sql

mysql

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.

like image 205
рüффп Avatar asked Jan 14 '13 10:01

рüффп


People also ask

How do I combine data from multiple tables into one table in SQL?

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).

How do I concatenate a table in SQL?

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.

Is it possible to combine like and in in a SQL Server query?

No, MSSQL doesn't allow such queries. You should use col LIKE '...' OR col LIKE '...' etc.


1 Answers

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.

like image 157
eggyal Avatar answered Sep 30 '22 20:09

eggyal