0

I am trying to convert a result set into a column set - rows to columns in MySQL. I am returning only one value from the multiple rows to put into the column depending on the row - as 1 record only - and the column heading corresponds simply to the row number that was returned.

Here is my tables and my code. I have two tables a primary table and an Intermediary Table for Many to Many relationship. Primary Table = locations - Columns Id, SeqNumber - the SeqNumber matches another system 1 for 1. Intermediary Table = boxlocations Columns locationId, otherTableID, IsSelected, otherProperty

The Result I would like to get is:

Should be exactly 1 ROW result with Columns representing each of the rows returned. The Data in the columns should follow this (If IsSelected=1 for a given row in the result, then the column should have the SeqNumber Else -1 ) I might mention that this table locations has only 14 rows and I only need this SeqNumber to be in columns from the query - and the columns need to match the # of rows returned from the query of the locations table.

SELECT      `locations`.`SeqNumber`, BL.IsSelected , 
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_1,          
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_2,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_3,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_4,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_5,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_6,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_7,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_8,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_9,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_10,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_11,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_12,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_13,
            SUM(CASE IsSelected WHEN 1 THEN SeqNumber ELSE -1 END) Location_Id_14  

FROM locations LEFT OUTER JOIN 
(SELECT * FROM boxlocations WHERE boxlocations.BoxID = boxid) As BL
 ON BL.locationID = locations.Id ORDER BY SeqNumber; 

I tried using MAX If as well ,MAX(IF(IsSelected = 1, Ndx, -1)) I tried using this SELECT locations.SeqNumber, BL.IsSelected , ( @curRank := @curRank + 1 ) As rank, but then I get unknown field rank.

There are lots of articles on SO - where I got the two examples from and also the rank

Does any one have a simple solution for this problem ? - I am open to adding stored procedures, generic procedures etc - the solution however must be ALL purely MySQL code.

/* EDIT 9-14-2016:3:20 */ Per request: Here is a dump of the two tables schema and data.

CREATE DATABASE  IF NOT EXISTS `DevDataBase` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `DevDataBase`;
-- MySQL dump 10.13  Distrib 5.7.9, for Win64 (x86_64)
--
-- Host: localhost    Database: DevDataBase
-- ------------------------------------------------------
-- Server version   5.7.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `boxlocations`
--

DROP TABLE IF EXISTS `boxlocations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `boxlocations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `boxID` int(11) NOT NULL DEFAULT '0' COMMENT 'References the Box.ID column of Table Boxs',
  `locationID` int(11) NOT NULL DEFAULT '0' COMMENT 'References the Location.ID column of Table Locations',
  `recipeId` int(11) NOT NULL,
  `IsSelected` int(1) DEFAULT '0' COMMENT 'IS the location enabled for the selected box.',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `FK_BOXID_BOXID_idx` (`boxID`),
  KEY `FK_LOCID_LOCID_idx` (`locationID`),
  CONSTRAINT `FK_BOXID_BOXID` FOREIGN KEY (`boxID`) REFERENCES `boxs` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_LOCID_LOCID` FOREIGN KEY (`locationID`) REFERENCES `locations` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='Intermediary Table [aka Associative Table] for boxs and leng';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `boxlocations`
--

LOCK TABLES `boxlocations` WRITE;
/*!40000 ALTER TABLE `boxlocations` DISABLE KEYS */;
INSERT INTO `boxlocations` VALUES (1,1,1,0,1),(2,2,1,0,1),(3,2,2,0,1),(4,2,20,0,1),(5,4,20,0,1),(6,4,10,0,1),(7,6,10,0,1),(8,6,11,0,0),(9,6,3,0,1),(10,6,5,0,1),(11,6,7,0,1),(12,6,4,0,1),(13,6,1,0,1),(14,1,8,0,1),(15,1,10,0,1),(16,1,12,0,1);
/*!40000 ALTER TABLE `boxlocations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `locations`
--

DROP TABLE IF EXISTS `locations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `locations` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(82) DEFAULT '""',
  `Min` decimal(11,3) DEFAULT '0.000',
  `Max` decimal(11,3) DEFAULT '0.000',
  `Nom` decimal(11,3) DEFAULT '0.000',
  `Actual_Real` decimal(11,3) DEFAULT '0.000',
  `Set_Default` int(11) DEFAULT '0',
  `Set_Enable` int(11) DEFAULT '0',
  `Visible` int(11) DEFAULT '0',
  `SeqNumber` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `locations`
--

LOCK TABLES `locations` WRITE;
/*!40000 ALTER TABLE `locations` DISABLE KEYS */;
INSERT INTO `locations` VALUES (1,'LOCATION1',0.000,0.000,0.000,0.000,1,0,0,1),(2,'LOCATION2',0.100,0.200,0.000,0.000,0,1,0,2),(3,'LOCATION3',0.000,0.000,0.000,0.000,0,0,0,3),(4,'LOCATION4',0.000,0.000,0.000,0.000,0,0,0,4),(5,'LOCATION5',0.000,0.000,0.000,0.000,0,0,0,5),(6,'LOCATION6',0.000,0.000,0.000,0.000,0,0,0,6),(7,'LOCATION7',0.000,0.000,0.000,0.000,0,0,0,7),(8,'LOCATION8',0.000,0.000,0.000,0.000,0,0,0,8),(9,'LOCATION9',0.000,0.000,0.000,0.000,0,0,0,9),(10,'LOCATION10',0.000,0.000,0.000,0.000,0,0,0,10),(11,'LOCATION11',0.000,0.000,0.000,0.000,0,0,0,11),(12,'LOCATION12',0.000,0.000,0.000,0.000,0,0,0,12),(13,'LOCATION13',0.000,0.000,0.000,0.000,0,0,0,13),(14,'LOCATION14',0.000,0.000,0.000,0.000,0,0,0,14),(15,'LOCATION15',0.000,0.000,0.000,0.000,0,0,0,15),(16,'LOCATION16',0.000,0.000,0.000,0.000,0,0,0,16),(17,'LOCATION17',0.000,0.000,0.000,0.000,0,0,0,17),(18,'LOCATION18',0.000,0.000,0.000,0.000,0,0,0,18),(19,'LOCATION19',0.000,0.000,0.000,0.000,0,0,0,19),(20,'LOCATION20',0.000,0.000,0.000,0.000,0,0,0,20);
/*!40000 ALTER TABLE `locations` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-09-14 15:48:50

AND HERE Are two results from different attempts. enter image description here

enter image description here

  • Show some sample data and sample output. – Rick James Sep 14 '16 at 20:00
  • @RickJames ok - I added the dump of the schema and the data. I also added two pictures showing my results and that is not what they should be at all . I am expecting the ndx column - there are only 14 rows in locations and ndx is a sequence number. 1-14. So I should see either an ndx value of the related location or -1 if IsSelected = 1. – StixO Sep 14 '16 at 21:31
  • All 14 columns will have the same value since the expressions are the same. – Rick James Sep 15 '16 at 1:21
1

Perhaps this is closer to what you want? It not, see if it gives you a clue about how to "pivot".

SELECT  
        SUM(if(ID = 1, locationID, 0)) Location_Id_1,          
        SUM(if(ID = 2, locationID, 0)) Location_Id_2,          
        SUM(if(ID = 3, locationID, 0)) Location_Id_3,          
        SUM(if(ID = 4, locationID, 0)) Location_Id_4,          
        SUM(if(ID = 5, locationID, 0)) Location_Id_5
    FROM boxlocations;

(MAX() should give the same result as SUM().)

  • This really is not the answer i was looking for - as the ID's could possibly change and I did not want the locationID but rather the SeqNumber. That said with a little effort this did allow me to simplify what I was doing. I ended up creating a Rank (at)curRank = 0; and then in subquery Select *,((at)curRank := (at)curRank +1) , so the SUM(IF became this: SUM(if(BL.IsSelected = 1 AND BL.Rank = 1, locations.SeqNumber, 0)) Location_Id_1, even though your answer was not what I wanted since it made it possible to resolve - I marked it as answer. Thx – StixO Sep 15 '16 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.