Dynamic Columns in SQL Select statement, keeping "undefined" values

Question

This is a new Question based on my previos question, which was marked as beeing a "duplicate" of the question mySQL - Create a New Table Using Data and Columns from Three Tables

This question looks very similiar, but has one essential part which is different I tried to use the solutions from the linked, but the Ansers does not fit to my problem.

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?

According too the linked question above, I tried also the following approach

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

The reason for this behavior is, that onle the item "Car" have all the three properties "Color", "Speed" and "Price" filled with values. The item "House" is skipped because it has a "Color" and a "Price", but obviously no "Speed".

So how to get the table in the wanted manner?

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 */;

OK, _now_ you do have a duplicate. Ekkehard, please figure out what answer works for you (I think mine is the best) then mark the other as a dup. (If needed, ask someone with more 'reputation' do do it). [_The dup_](http://stackoverflow.com/questions/43684923/dynamic-columns-in-sql-select-statement) — Rick James, Apr 30 '17 at 19:09

score 1 · Answer 1 · answered Apr 29 '17 at 12:40

You are very close. You need to join your key/value table itemproperties once for each distinct key (property) you need to retrieve. The thing is, you need to use LEFT JOIN. Plain inner JOIN suppresses the output row when the join criterion is unmet.

Try this.

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

The expressions selecting the Name values (z.B. p3.Name = 'Price') go in your ON clause rather than your WHERE clause.

Thank you very much, your select statement indeed works fine! I was sure that a "left join" was needed (as seen in my first approach), but what I really missed was the part `AND (p1.Name = 'Color')`. Now I am able to proceed with my real live problem. — Ekkehard Domning, Apr 29 '17 at 16:38

score 0 · Answer 2 · answered Apr 29 '17 at 12:31

0

The below answer is for SQL SERVER but I hope it could be of some help to you. I am using PIVOT function to achieve the result.You particularly can a similar function in MariaDB.

WITH cte as(   
SELECT i.ID as ItemID, i.Name as ItemName,p1.name,p1.value
FROM items as i
JOIN itemproperties AS p1 ON (i.ID=p1.ItemID)
)

select * from cte
PIVOT
(
    max(value) for Name in ([Color],[Speed],[Price])
)A

REXTESTER DEMO

answered Apr 29 '17 at 12:31

Hexxx

413
5
13

@O.Jones This is what a quick google search returned. [link](http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/) – Hexxx Apr 29 '17 at 12:39
CTEs are coming in MariaDB 10.2 and MySQL 8.0.1. But the `PIVOT` keyword is not. – Rick James Apr 30 '17 at 19:06

Dynamic Columns in SQL Select statement, keeping "undefined" values

2 Answers2

Linked