MySql rows in to columns (but dynamic rows)

Question

This is my SQL table and data. http://sqlfiddle.com/#!9/effe2

CREATE TABLE IF NOT EXISTS `CustomValue` (
  `id` int(11) NOT NULL,
  `customFieldId` int(11) NOT NULL,
  `relatedId` int(11) NOT NULL,
  `fieldValue` text COLLATE utf8_unicode_ci,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `CustomValue` (`id`, `customFieldId`, `relatedId`, `fieldValue`, `createdAt`) VALUES
(1, 10, 4031, NULL, '2015-11-05 04:25:00'),
(2, 14, 4031, 'adsas@das.sadsa', '2015-11-05 04:25:00'),
(3, 13, 4031, '456', '2015-11-05 04:25:00'),
(4, 16, 4031, '2015-11-09', '2015-11-05 04:25:00'),
(5, 9, 4031, '456', '2015-11-05 04:25:00'),
(6, 11, 4031, 'dsasda', '2015-11-05 04:25:00'),
(7, 15, 4031, '1', '2015-11-05 04:25:00');

Right now it is as,

id  customFieldId   relatedId   fieldValue         createdAt
1   10                 4031     (null)             November, 05 2015 04:25:00
2   14                 4031     adsas@das.sadsa    November, 05 2015 04:25:00
3   13                 4031     456                November, 05 2015 04:25:00
4   16                 4031     2015-11-09         November, 05 2015 04:25:00
5   9                  4031     456                November, 05 2015 04:25:00
6   11                 4031     dsasda             November, 05 2015 04:25:00
7   15                 4031     1                  November, 05 2015 04:25:00

I need to group by relatedId and and get the final output as 1 row for each relatedId.

This is the reference table.

CREATE TABLE IF NOT EXISTS `CustomField` (
  `id` int(11) NOT NULL,
  `customTypeId` int(11) NOT NULL,
  `fieldName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `relatedTable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `defaultValue` text COLLATE utf8_unicode_ci,
  `sortOrder` int(11) NOT NULL DEFAULT '0',
  `enabled` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `listItemTag` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `required` char(1) COLLATE utf8_unicode_ci DEFAULT '0',
  `onCreate` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `onEdit` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `onView` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `listValues` text COLLATE utf8_unicode_ci,
  `label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `htmlOptions` text COLLATE utf8_unicode_ci
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `CustomField` (`id`, `customTypeId`, `fieldName`, `relatedTable`, `defaultValue`, `sortOrder`, `enabled`, `listItemTag`, `required`, `onCreate`, `onEdit`, `onView`, `listValues`, `label`, `htmlOptions`) VALUES
(13, 1, 'HOMEEMAIL', 'people', '', 0, '1', NULL, '1', '1', '1', '1', NULL, 'Home Email', ''),
(9, 1, 'LANDPHONENO', 'people', '', 0, '1', NULL, '1', '1', '1', '1', NULL, 'Land Phone No', ''),
(10, 12, 'ABOUTME', 'people', '', 0, '1', NULL, '0', '1', '1', '1', NULL, 'About Me', ''),
(11, 3, 'PHONENUMBER2', 'people', '', 0, '1', NULL, '1', '1', '1', '1', NULL, 'Phone Number 2', ''),
(14, 3, 'ALTERNATEEMAIL', 'people', '', 0, '1', NULL, '1', '1', '1', '1', NULL, 'Alternate Email', ''),
(15, 11, 'SCHOOLING?', 'people', '', 0, '1', NULL, '1', '1', '1', '1', NULL, 'Schooling?', ''),
(16, 4, 'JOINDATE', 'people', '', 0, '1', NULL, '1', '1', '1', '1', NULL, 'Join Date', '');

The final output should be,

relatedId | Alternate Email | Home Email | Join Date | Land Phone No | Phone Number 2 | Schooling?
--------------------------------------------------------------------------------------------------
4031      | adsas@das.sadsa | 456        | 2015-11-09| 456           | dsasda         | 1

relatedId | Alternate Email | Home Email | Join Date | Land Phone No | Phone Number 2 | Schooling? | Interest
--------------------------------------------------------------------------------------------------
4033      | adsas@das.sadsa | 456        | 2015-11-09| 456           | dsasda         | 1 | Drawing

The output of phpmyadmin

As you have multiple identical values for `relatedId`, which row should be used as a result? — Smutje, Nov 05 '15 at 09:20
You are trying to pivot the table: Have a look at this question: http://stackoverflow.com/questions/7674786/mysql-pivot-table — AgeDeO, Nov 05 '15 at 09:31
@AgeDeO Its not exactly Pivot table. something similar but with a joined table. i dont have a fixed column to do a CASE base query as in the referred link. (WHEN P.`action`='EMAIL') — dev1234, Nov 05 '15 at 09:33
As you can see the expected final output, column names are taken from CustomField table and values from it are taken from CustomValue table. This is an example of a record. (relatedId, Alternate Email, Home Email, Join Date, Land Phone No, Phone Number 2,Schooling) — dev1234, Nov 05 '15 at 09:36

score 1 · Accepted Answer · answered Nov 05 '15 at 09:42

1

What you need here is PIVOT rows into columns, MySQL however doesn't have a native pivot operator like SQL Server or Oracle for example. But you can use CASE expression with group by to do this like this:

SELECT 
  v.relatedId, v.CreatedAt,
  MAX(IF(f.fieldName = 'ABOUTME', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'ABOUTME',
  MAX(IF(f.fieldName = 'ALTERNATEEMAIL', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'ALTERNATEEMAIL',
  MAX(IF(f.fieldName = 'HOMEEMAIL', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'HOMEEMAIL',
  MAX(IF(f.fieldName = 'JOINDATE', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'JOINDATE',
  MAX(IF(f.fieldName = 'LANDPHONENO', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'LANDPHONENO',
  MAX(IF(f.fieldName = 'PHONENUMBER2', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'PHONENUMBER2',
  MAX(IF(f.fieldName = 'SCHOOLING?', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS 'SCHOOLING?'
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId 
GROUP BY   v.relatedId, v.CreatedAt;

And to do it dynamically you have to do it with dynamic sql like this:

SET @Colvalues = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(f.fieldName = ''',
      f.fieldName, ''', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS ', '''', f.fieldName , '''')
  ) INTO @Colvalues
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId;


SET @sql = CONCAT('SELECT 
    v.relatedId, v.CreatedAt, ', @Colvalues , '
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId 
GROUP BY   v.relatedId, v.CreatedAt;');

PREPARE stmt 
FROM @sql;

EXECUTE stmt;

Note that:

If the field value is null it will set the value from the default value field, thats what COALESCE(v.fieldValue, f.defaultValue) do.
You can eliminate NULL values like in the field Aboutname case, by adding a WHERE v.fieldValue IS NOT NULL.

This will give you:

answered Nov 05 '15 at 09:42

Mahmoud Gamal

78,257
17
139
164

Thanks for this input. however i dont get any results for dynamic sql solution except the messages from phpmyadmin as Rows: 1. btw i am new to dynamic sql. – dev1234 Nov 05 '15 at 09:55
@dev1234 - What phpmyadmin told you? Is there any errors returned? – Mahmoud Gamal Nov 05 '15 at 09:58
No errors returned. i took a screenshot and updated the question. please check. the result is not displayed. – dev1234 Nov 05 '15 at 10:10
@dev1234 - Yes, I saw it, so the query returned two rows, but I don't know why it doesn't show them. Can you please run it on MySQL directly using MySQL Workbench? – Mahmoud Gamal Nov 05 '15 at 10:12
there is a problem in this answer. when you have more records like 4032, 4033 and they have another extra column because the number of columns are dynamic. but those records are not considered and still it shows only record only. – dev1234 Nov 06 '15 at 09:49
@dev1234 - The query works perfectly on the sql fiddle you gave, but the in the sql fiddle I can't see the problem and I can see only 4031 and 4033 where is the 4032 and what exactly the problem, I don't get you?? here is the query running fine on your sql fiddle: http://sqlfiddle.com/#!9/fb5a7/1. Please edit and show me the query returning the wrong results. – Mahmoud Gamal Nov 06 '15 at 11:02
sorry it works fine. i had a problem with MySql workbrench. Btw is it possible for you to create a View to store the output of this query. and when ever there is a new custom field, the View will get updated. here is my final solution. http://sqlfiddle.com/#!9/1a579/3 This is the because i a screen of a grid view where these records are listed, so user will be able to search based on PHONENUMBER2 or ALTERNATEEMAIL or any other custom columns. Thanks. – dev1234 Nov 06 '15 at 12:18
1

@dev1234 You can't put it in a view because of the dynamic SQL. You can however, put it in a stored procedure. – Mahmoud Gamal Nov 06 '15 at 15:13

MySql rows in to columns (but dynamic rows)

1 Answers1