MySql行到列（但动态行）

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

这是我的SQL表和数据。 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.

我需要通过relatedId进行分组,并为每个relatedId获取最终输出为1行。

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

phpmyadmin的输出

1 个解决方案

#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:

你需要的是PIVOT行到列,但是MySQL没有像SQL Server或Oracle那样的本地数据透视运算符。但您可以使用带有group by的CASE表达式来执行此操作:

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:

要动态地执行它,你必须使用动态sql这样做:

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.

如果字段值为null,它将从默认值字段设置值,这就是COALESCE(v.fieldValue,f.defaultValue)所做的。

You can eliminate NULL values like in the field Aboutname case, by adding a WHERE v.fieldValue IS NOT NULL.

您可以通过添加WHERE v.fieldValue IS NOT NULL来消除NULL值,例如字段Aboutname情况。

This will give you:

这会给你:

#1