Dynamic Columns in SQL Select statement

Question

This Question was marked as beeing a "duplicate" of the question mySQL - Create a New Table Using Data and Columns from Three Tables

I think the question is different, because there are three tables in action, in my question are two. There the question is how to create a new table, here the question is to create an output. But thanks for pointing, maybe it's closer to my problem than I expecting.

I tried to use the solutions from the "Duplicate"-Question, but the Ansers and the problems are not very simmilar.

According too the "Duplicated"-Question, I tried

SELECT i.ID as ItemID, i.Name as ItemName, 
       p1.Value AS Color, p2.Value AS Speed, p3.Value AS Price
FROM items as i
JOIN itemproperties AS p1 ON (i.ID=p1.ItemID)
JOIN itemproperties AS p2 ON (i.ID=p2.ItemID)
JOIN itemproperties AS p3 ON (i.ID=p3.ItemID)
WHERE (p1.Name = 'Color') and (p2.Name = 'Speed') and (p3.Name = 'Price')

But the result is only one line

ItemID, ItemName, Color, Speed, Price
1,      Car,      blue,    200, 50000

Instead of the expected four lines, containing the value "(NULL)" at the places where no property name is found.

So IMHO the "Duplicate"-Question is different and did not answer my question!

Original question:

My personal SQL skills are limited. After looking around for several days, I did not find any working solution for my database query problem below. I attached the full example Database SQL-text at the end of this question. My example database (made with MariaDB) contains two tables:

items, and
itemproperties.

For each item only an item.ID and an item.Name is defined. (In a real life example, the name would be defined to be unique.)

For each item an dynamic user defined set of properties is possible. These properties are defined as a name-value-pair. For example, for an item named "Banana" a property "Color" with the value "yellow" may exists.

It is only valid to have one "Color" property for one item, so that not two different colors could be assigned to one item.

(In my real world problem the property names contains only two characters, so an additional property name table is not necessary, and subsequently for the ease of showing the problem not used in the example).

The example data for the items table:

ID, Name
1,  Car
2,  House
3,  Homer
4,  Earth

And a total of nine properties for the items above are defined. The entry "(NULL)" are indicating that this property is not defined for a given item

ItemID, ItemName, Color,    Speed,  Price
1,      Car,      blue,       200,  50000
2,      House,    red,     (NULL), 250000
3,      Homer,    yellow,       5, (NULL)
4,      Earth,    blue,    108000, (NULL)

Unfortunally my select statement

SELECT items.ID as ItemID, items.Name as ItemName,
CASE WHEN (itemproperties.Name = 'Color')
       THEN itemproperties.Value
       #ELSE NULL
END as Color,
CASE WHEN (itemproperties.Name = 'Speed')
       THEN itemproperties.Value
       #ELSE NULL
END as Speed,
CASE WHEN (itemproperties.Name = 'Price')
       THEN itemproperties.Value
       #ELSE NULL
END as Price
FROM items left join itemproperties 
ON  (items.ID=itemproperties.ItemID)

returns the data like this

ItemID, ItemName, Color,   Speed, Price
1,      Car,      blue,   (NULL), (NULL)
1,      Car,      (NULL),  200,   (NULL)
1,      Car,      (NULL), (NULL), 50000
2,      House,    red,    (NULL), (NULL)
2,      House,    (NULL), (NULL), 250000
3,      Homer,    yellow, (NULL), (NULL)
3,      Homer,    (NULL),      5, (NULL)
4,      Earth,    blue,   (NULL), (NULL)
4,      Earth,    (NULL), 108000, (NULL)

Question: How to write the select statement to get tha data in a collated form, one row for each item? Thank you very much!

Greetings Ekkehard

Database definition:

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server Version:               10.1.13-MariaDB - mariadb.org binary distribution
-- Server Betriebssystem:        Win32
-- HeidiSQL Version:             9.4.0.5125
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Exportiere Datenbank Struktur für DynamicColTest
CREATE DATABASE IF NOT EXISTS `dynamiccoltest` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `DynamicColTest`;

-- Exportiere Struktur von Tabelle DynamicColTest.itemproperties
CREATE TABLE IF NOT EXISTS `itemproperties` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the property',
  `ItemID` int(10) unsigned DEFAULT '0' COMMENT 'ID of the Item this property belongs to',
  `Name` varchar(20) DEFAULT '0' COMMENT 'Name of the property',
  `Value` varchar(20) DEFAULT '0' COMMENT 'Value of the property',
  UNIQUE KEY `Schlüssel 3` (`Name`,`ItemID`),
  KEY `Schlüssel 1` (`ID`),
  KEY `FK_itemproperties_items` (`ItemID`),
  CONSTRAINT `FK_itemproperties_items` FOREIGN KEY (`ItemID`) REFERENCES `items` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT='The properties of the items';

-- Exportiere Daten aus Tabelle DynamicColTest.itemproperties: ~9 rows (ungefähr)
DELETE FROM `itemproperties`;
/*!40000 ALTER TABLE `itemproperties` DISABLE KEYS */;
INSERT INTO `itemproperties` (`ID`, `ItemID`, `Name`, `Value`) VALUES
    (1, 1, 'Color', 'blue'),
    (1, 4, 'Color', 'blue'),
    (1, 2, 'Color', 'red'),
    (2, 3, 'Color', 'yellow'),
    (3, 1, 'Speed', '200'),
    (3, 4, 'Speed', '108000'),
    (4, 3, 'Speed', '5'),
    (5, 1, 'Price', '50000'),
    (5, 2, 'Price', '250000');
/*!40000 ALTER TABLE `itemproperties` ENABLE KEYS */;

-- Exportiere Struktur von Tabelle DynamicColTest.items
CREATE TABLE IF NOT EXISTS `items` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Item ID',
  `Name` varchar(25) DEFAULT '0' COMMENT 'Name of the Item',
  KEY `Schlüssel 1` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT='Contains all Items, with a minimum of definitions';

-- Exportiere Daten aus Tabelle DynamicColTest.items: ~4 rows (ungefähr)
DELETE FROM `items`;
/*!40000 ALTER TABLE `items` DISABLE KEYS */;
INSERT INTO `items` (`ID`, `Name`) VALUES
    (1, 'Car'),
    (2, 'House'),
    (3, 'Homer'),
    (4, 'Earth');
/*!40000 ALTER TABLE `items` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

See also http://stackoverflow.com/questions/43695483/dynamic-columns-in-sql-select-statement-keeping-undefined-values — Rick James, Apr 30 '17 at 19:09

score 0 · Answer 1 · answered Apr 30 '17 at 18:51

3 JOINs vs 2 is not a significant difference.

Wanting NULLs for missing attributes is a difference. (Hence the re-opening.)

You need LEFT JOIN instead of JOIN and move the WHERE clauses into the ON clauses. (This is one of the few cases where it really matters whether you put a condition in ON versus WHERE.)

SELECT  i.ID as ItemID, i.Name as ItemName,
        p1.Value AS Color, p2.Value AS Speed, p3.Value AS Price
    FROM  items as i
    LEFT JOIN  itemproperties AS p1  ON i.ID=p1.ItemID  AND  p1.Name = 'Color'
    LEFT JOIN  itemproperties AS p2  ON i.ID=p2.ItemID  AND  p2.Name = 'Speed'
    LEFT JOIN  itemproperties AS p3  ON i.ID=p3.ItemID  AND  p3.Name = 'Price'
    WHERE  p1.Value IS NOT NULL
       OR  p2.Value IS NOT NULL
       OR  p3.Value IS NOT NULL;

Performance will not be nice, but then that is a generic problem with Entity-Attribute-Value schemas.

One thing that will help is to have this on itemproperties:

PRIMARY KEY(ItemID, Name)  -- in this order

and to use ENGINE=InnoDB.

If you need further discussion, please provide SHOW CREATE TABLE and EXPLAIN SELECT.

(One of the other attempts could have been fixed via MAX and GROUP BY.)

Thank you for your answer, which is working fine. Regarding the performance issue. In my real problem, the amount of possible "variable names" is around 300, while the actual currently used is around 3-4. I tried to avoid creating a huge table, which will be almost empty, or stress the future workers on the system in constantly updating the database definition. — Ekkehard Domning, May 02 '17 at 09:44

score 0 · Answer 2 · answered Apr 30 '17 at 18:53

You can do this without a join per attribute this way:

SELECT i.ID, i.Name,
  MAX(IF(p.Name='Color', p.value, NULL)) AS Color,
  MAX(IF(p.Name='Speed', p.value, NULL)) AS Speed,
  MAX(IF(p.Name='Price', p.value, NULL)) AS Price
FROM items i
LEFT JOIN itemproperties p 
ON  (i.ID=p.ItemID)
GROUP BY i.ID

Output:

+----+-------+--------+--------+--------+
| ID | Name  | Color  | Speed  | Price  |
+----+-------+--------+--------+--------+
|  1 | Car   | blue   | 200    | 50000  |
|  2 | House | red    | NULL   | 250000 |
|  3 | Homer | yellow | 5      | NULL   |
|  4 | Earth | blue   | 108000 | NULL   |
+----+-------+--------+--------+--------+

But working with EAV data is always awkward and risky. Avoid it if you can.

PS: Also you should declare a primary key constraint for your items table.

alter table items add primary key (ID);

And finally this is also working fine. I will try to implement all the solutions to see which may fit in perfomance best to my needs. Thank you all! — Ekkehard Domning, May 02 '17 at 09:50
Glad to help. As a reminder, it is customary to upvote answers that are helpful to you, and mark as accepted answer (the green checkmark) the one that is best. — Bill Karwin, May 02 '17 at 14:41

Dynamic Columns in SQL Select statement

2 Answers2

Linked