在MySQL中将行记录转换为列 [英] Converting rows records to columns in mySQL
问题描述
我正在尝试将行数据转换为列.数据将从不同的表中提取.我尝试使用PIVOT，但效果不佳.
I'm trying to convert row data to columns. Data will be extracted from different tables. I tried using PIVOTbut I'm not much successful.
让我们将column#1视为每个表中的主键.
Lets consider column#1 as primary key in every table.
CREATE TABLE Table_pivot_01
([SSN ID] int, [Citizen_name] varchar(5), [Company] varchar(4))
;
INSERT INTO Table_pivot_01
([SSN ID], [Citizen_name], [Company])
VALUES
(12345, 'John', 'XYZ'),
(12346, 'Tom', 'ABC'),
(12347, 'Jerry', 'QWER'),
(12348, 'Joe', 'PQR'),
(12349, 'Josh', NULL)
;
CREATE TABLE Table_pivot_02
([Serial] int, [SSN_ID] int, [Family_details] varchar(9), [Family_members_name] varchar(10))
;
INSERT INTO Table_pivot_02
([Serial], [SSN_ID], [Family_details], [Family_members_name])
VALUES
(1010, 12345, 'Spouse', 'Mari'),
(1011, 12345, 'Child - 1', 'John Jr. 1'),
(1012, 12345, 'Child - 2', 'John Jr. 2'),
(1013, 12346, 'Spouse', 'Ken'),
(1014, 12347, 'Spouse', 'Suzen'),
(1015, 12347, 'Child - 1', 'Jerry Jr.1'),
(1016, 12347, 'Child - 2', 'Jerry Jr.2'),
(1017, 12347, 'Child - 3', 'Jerry Jr.3'),
(1018, 12348, 'Child - 1', 'Joe Jr.1'),
(1019, 12348, 'Child - 2', 'Joe Jr.2'),
(1020, 12349, 'Spouse', 'Zoe'),
(1021, 12349, 'Child - 1', 'Josh Jr.1'),
(1022, 12349, 'Child - 2', 'Josh Jr.2')
;
CREATE TABLE Table_pivot_03
([Row] int, [SSN_ID] int, [Address_type] varchar(8), [Address] varchar(22), [PhoneNumber_type] varchar(6), [PhoneNumber] varchar(18))
;
INSERT INTO Table_pivot_03
([Row], [SSN_ID], [Address_type], [Address], [PhoneNumber_type], [PhoneNumber])
VALUES
(121, 12345, 'Present', 'Address_John_Present', 'Home', 'John_Home_phone'),
(122, 12345, 'Office', 'Address_John_Office', 'Office', 'John_Office_phone'),
(123, 12345, 'Perement', 'Address_John_Perement', 'Fax', 'John_FAX_phone'),
(124, 12346, 'Present', 'Address_Tom_Present', 'Home', 'Tom_Home_phone'),
(125, 12346, 'Office', 'Address_Tom_Office', 'Office', 'Tom_Office_phone'),
(126, 12347, 'Office', 'Address_Jerry_Office', 'Home', 'Jerry_Home_phone'),
(127, 12347, 'Perement', 'Address_Jerry_Perement', 'Office', 'Jerry_Office_phone'),
(128, 12348, 'Present', 'Address_Joe_Present', 'Home', 'Joe_Home_phone'),
(129, 12348, 'Office', 'Address_Joe_Office', 'Office','Joe_Office_phone'),
(130, 12348, 'Perement' , 'Address_Josh_Perement','','' ),
(131, 12349, 'Present','Address_Josh_Present','Home','Josh_Home_phone'),
(132, 12349, 'Perement', 'Address_Josh_Perement' , 'Fax' ,'Josh_FAX_phone');
表架构: http://rextester.com/MSXK16689
预期输出为:
如何有效地生成结果?
推荐答案
MySQL版本
您已经声明过尝试使用PIVOT，但是MySQL没有PIVOT函数.在MySQL中，您需要将聚合函数与条件逻辑语句(如CASE...WHEN或类似的东西)一起使用.您还需要旋转几个表和几个不同的列，这使事情变得有些复杂.似乎您还需要创建数量未知的新列，这又增加了另一层复杂性.
You've stated that you have tried using PIVOT but MySQL doesn't have a PIVOT function. In MySQL you need to use an aggregate function along with a conditional logic statement like CASE...WHEN or something similar. You also have several tables and several different columns you need to pivot which complicates this a bit. It also seems that you have an unknown number of new columns that need to be created, which adds another layer of complexity.
如果您知道要在最终结果中显示的所有列，则可以轻松键入该查询的版本，如下所示:
If you know all of the columns you want to be displayed in the final result, then you can easily type up a version of this query to be something like:
select
p1.`SSN_ID`,
p1.Citizen_name,
p1.Company,
max(case when p2.Family_details = 'Spouse' then Family_members_name end) Spouse,
max(case when p2.Family_details = 'Child - 1' then Family_members_name end) Child1,
max(case when p2.Family_details = 'Child - 2' then Family_members_name end) Child2,
max(case when p2.Family_details = 'Child - 3' then Family_members_name end) Child3,
max(case when p2.Family_details = 'Child - 4' then Family_members_name end) Child4,
max(case when p3.Address_type = 'Present' then p3.Address end) PresentAddress,
max(case when p3.Address_type = 'Office' then p3.Address end) OfficeAddress,
max(case when p3.Address_type = 'Perement' then p3.Address end) PermAddress,
max(case when p3.PhoneNumber_type = 'Home' then p3.PhoneNumber end) HomePhone,
max(case when p3.PhoneNumber_type = 'Office' then p3.PhoneNumber end) OfficePhone,
max(case when p3.PhoneNumber_type = 'Fax' then p3.PhoneNumber end) FaxPhone
from Table_pivot_01 p1
left join Table_pivot_02 p2
on p1.`SSN_ID` = p2.`SSN_ID`
left join Table_pivot_03 p3
on p1.`SSN_ID` = p3.`SSN_ID`
group by p1.`SSN_ID`,
p1.Citizen_name,
p1.Company;
基本上，您在max(case...语句中创建一个新列，它将显示该值.如前所述，如果您想将未知的值用作列，这会变得更加复杂.在MySQL中，您需要使用 Prepared Statement ，因此您可以使用动态SQL.您的代码看起来像这样:
Basically you create a new column in a max(case... statement and it will display the value. As mentioned, this gets a bit more complicated if you have unknown values you want as columns. In MySQL you need to use a Prepared Statement so you can use dynamic SQL. Your code would looks sort of like this:
SET @sql = NULL;
SET @sql1 = NULL;
SET @sql2 = NULL;
SET @sql3 = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' max(case when p2.Family_details = ''',
Family_details,
''' then Family_members_name end) AS `',
Family_details, '`'
)
) INTO @sql1
FROM Table_pivot_02;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' max(case when p3.Address_type = ''',
Address_type,
''' then Address end) AS `',
Address_type, '`'
)
) INTO @sql2
FROM Table_pivot_03;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' max(case when p3.PhoneNumber_type = ''',
PhoneNumber_type,
''' then PhoneNumber end) AS `',
PhoneNumber_type, '`'
)
) INTO @sql3
FROM Table_pivot_03
where PhoneNumber_type <> '';
SET @sql = CONCAT('SELECT p1.`SSN_ID`,
p1.Citizen_name,
p1.Company, ', @sql1, ',', @sql2, ',', @sql3, '
from Table_pivot_01 p1
left join Table_pivot_02 p2
on p1.`SSN_ID` = p2.`SSN_ID`
left join Table_pivot_03 p3
on p1.`SSN_ID` = p3.`SSN_ID`
group by p1.`SSN_ID`,
p1.Citizen_name,
p1.Company');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
在此过程中，您将创建由max(case...语句组成的长字符串，这些字符串会串联在一起，然后由数据库引擎执行.可能会有更简单的方法来获得所需的结果，但这确实可行.我已经在 rextester 上创建了一个演示，以显示代码.两者都会产生结果:
In this you are creating a long string of the max(case... statements that get concatenated together to then be executed by the database engine. There may be easier ways to get the result you want, but this does work. I've created a demo on rextester to show the code. Both of these produce a result:
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
| Row | SSN_ID | Citizen_name | Company | Spouse | Child - 1 | Child - 2 | Child - 3 | Present | Office | Perement | Home | Office | Fax |
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
| 1 | 12345 | John | XYZ | Mari | John Jr. 1 | John Jr. 2 | NULL | Address_John_Present | Address_John_Office | Address_John_Perement | John_Home_phone | John_Office_phone | John_FAX_phone |
| 2 | 12346 | Tom | ABC | Ken | NULL | NULL | NULL | Address_Tom_Present | Address_Tom_Office | NULL | Tom_Home_phone | Tom_Office_phone | NULL |
| 3 | 12347 | Jerry | QWER | Suzen | Jerry Jr.1 | Jerry Jr.2 | Jerry Jr.3 | NULL | Address_Jerry_Office | Address_Jerry_Perement | Jerry_Home_phone | Jerry_Office_phone | NULL |
| 4 | 12348 | Joe | PQR | NULL | Joe Jr.1 | Joe Jr.2 | NULL | Address_Joe_Present | Address_Joe_Office | Address_Josh_Perement | Joe_Home_phone | Joe_Office_phone | NULL |
| 5 | 12349 | Josh | NULL | Zoe | Josh Jr.1 | Josh Jr.2 | NULL | Address_Josh_Present | NULL | Address_Josh_Perement | Josh_Home_phone | NULL | Josh_FAX_phone |
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
根据您的评论(每个人可能拥有多个电话号码类型)，您需要创建一个
Based on your comment that you might have more than one phone number type per person, you'll need to create a row number for each group of phone types. Unfortunately, again MySQL doesn't have windowing function so you'll need to use user defined variables to get the final result. When you query for PhoneNumber_type you'll need to use something like:
select *
from
(
select SSN_ID, PhoneNumber_type, PhoneNumber,
@num:= case when @group = SSN_ID and @type = PhoneNumber_type then @num +1 else if(@group := SSN_ID, 1, 1) end rn,
@group:= SSN_ID,
@type:=PhoneNumber_type
from Table_pivot_03 t
CROSS JOIN (select @num:=0, @group:=null, @type:=null) c
where t.PhoneNumber_type <> ''
order by SSN_ID, PhoneNumber_type
) as x;
这将为每种用户和电话类型创建一个行号值.然后将其集成到动态SQL代码中:
This creates a row number value for each user and phone type. You'd then integrate this into the dynamic SQL code:
SET @sql = NULL;
SET @sql1 = NULL;
SET @sql2 = NULL;
SET @sql3 = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' max(case when p2.Family_details = ''',
Family_details,
''' then Family_members_name end) AS `',
Family_details, '`'
)
) INTO @sql1
FROM Table_pivot_02;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' max(case when p3.Address_type = ''',
Address_type,
''' then Address end) AS `',
Address_type, '`'
)
) INTO @sql2
FROM Table_pivot_03;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' max(case when p.PhoneNumber_type = ''',
PhoneNumber_type,
''' and rn = ', rn, ' then p.PhoneNumber end) AS `',
PhoneNumber_type, rn, '`'
)
) INTO @sql3
FROM
(
select SSN_ID, PhoneNumber_type, PhoneNumber,
@num:= case when @group = SSN_ID and @type = PhoneNumber_type then @num +1 else if(@group := SSN_ID, 1, 1) end rn,
@group:= SSN_ID,
@type:=PhoneNumber_type
from Table_pivot_03 t
CROSS JOIN (select @num:=0, @group:=null, @type:=null) c
where t.PhoneNumber_type <> ''
order by SSN_ID, PhoneNumber_type
) as x;
SET @sql = CONCAT('SELECT p1.`SSN_ID`,
p1.Citizen_name,
p1.Company, ', @sql1, ',', @sql2, ',', @sql3, '
from Table_pivot_01 p1
left join Table_pivot_02 p2
on p1.`SSN_ID` = p2.`SSN_ID`
left join Table_pivot_03 p3
on p1.SSN_ID = p3.SSN_Id
left join
(
select SSN_ID, PhoneNumber_type, PhoneNumber,
@num:= case when @group = SSN_ID and @type = PhoneNumber_type then @num +1 else if(@group := SSN_ID, 1, 1) end rn,
@group:= SSN_ID,
@type:=PhoneNumber_type
from Table_pivot_03 t
CROSS JOIN (select @num:=0, @group:=null, @type:=null) c
where t.PhoneNumber_type <> ''''
order by SSN_ID, PhoneNumber_type
) as p
on p1.SSN_ID = p.SSN_Id
group by p1.`SSN_ID`,
p1.Citizen_name,
p1.Company');
#select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
查看其他演示.
SQL Server版本
由于您已经说过需要SQL Server版本，因此这里就是该版本. SQL Server具有一些使该操作更加容易的功能，包括PIVOT函数，UNPIVOT函数以及诸如row_number之类的窗口函数.这是查询的静态版本，其中只有几列被透视:
Since you've now said that you need a SQL Server version here is that version. SQL Server has a few features that make doing this significantly easier including a PIVOT function, UNPIVOT function, and windowing functions like row_number. Here's a static version of the query with just a few columns pivoted:
select SSN_ID,
Citizen_name,
Company,
Spouse, [Child - 1], [Child - 2], [Child - 3], [Child - 4]
from
(
select SSN_ID,
Citizen_name,
Company,
col,
value
from
(
select
p1.SSN_ID,
p1.Citizen_name,
p1.Company,
p2.Family_details,
p2.Family_members_name,
p3.Address_type,
p3.Address,
PhoneNumber_type = p.PhoneNumber_type + cast(p.rn as varchar(10)),
p.PhoneNumber
from Table_pivot_01 p1
left join Table_pivot_02 p2
on p1.SSN_ID = p2.SSN_ID
left join Table_pivot_03 p3
on p1.SSN_ID = p3.SSN_ID
left join
(
select SSN_ID, PhoneNumber_type, PhoneNumber,
rn = row_number() over(partition by SSN_ID, PhoneNumber_type order by SSN_ID, PhoneNumber_type)
from Table_pivot_03
where PhoneNumber_type <> ''
) p
on p1.SSN_ID = p.SSN_ID
) d
cross apply
(
select 'Family_details', Family_details, Family_members_name union all
select 'Address_type', Address_type, Address union all
select 'PhoneNumber_type', PhoneNumber_type, PhoneNumber
) c(orig, col, value)
) src
pivot
(
max(value)
for col in (Spouse, [Child - 1], [Child - 2], [Child - 3], [Child - 4])
) piv
然后，如果您需要动态sql版本，代码将如下所示:
Then if you need a dynamic sql version the code would be like:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Col)
from
(
select col, ord
from
(
select
p1.SSN_ID,
p1.Citizen_name,
p1.Company,
p2.Family_details,
p2.Family_members_name,
p3.Address_type,
p3.Address,
PhoneNumber_type = p.PhoneNumber_type + cast(p.rn as varchar(10)),
p.PhoneNumber
from Table_pivot_01 p1
left join Table_pivot_02 p2
on p1.SSN_ID = p2.SSN_ID
left join Table_pivot_03 p3
on p1.SSN_ID = p3.SSN_ID
left join
(
select SSN_ID, PhoneNumber_type, PhoneNumber,
rn = row_number() over(partition by SSN_ID, PhoneNumber_type order by SSN_ID, PhoneNumber_type)
from Table_pivot_03
where PhoneNumber_type <> ''
) p
on p1.SSN_ID = p.SSN_ID
) d
cross apply
(
select 'Family_details', Family_details, Family_members_name, 1 union all
select 'Address_type', Address_type, Address, 2 union all
select 'PhoneNumber_type', PhoneNumber_type, PhoneNumber, 3
) c(orig, col, value, ord)
) d
group by col, ord
order by ord, col
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') ,1,1,'');
set @query = N'SELECT ' + @cols + N' from
(
select SSN_ID,
Citizen_name,
Company,
col,
value
from
(
select
p1.SSN_ID,
p1.Citizen_name,
p1.Company,
p2.Family_details,
p2.Family_members_name,
p3.Address_type,
p3.Address,
PhoneNumber_type = p.PhoneNumber_type + cast(p.rn as varchar(10)),
p.PhoneNumber
from Table_pivot_01 p1
left join Table_pivot_02 p2
on p1.SSN_ID = p2.SSN_ID
left join Table_pivot_03 p3
on p1.SSN_ID = p3.SSN_ID
left join
(
select SSN_ID, PhoneNumber_type, PhoneNumber,
rn = row_number() over(partition by SSN_ID, PhoneNumber_type order by SSN_ID, PhoneNumber_type)
from Table_pivot_03
where PhoneNumber_type <> ''''
) p
on p1.SSN_ID = p.SSN_ID
) d
cross apply
(
select ''Family_details'', Family_details, Family_members_name union all
select ''Address_type'', Address_type, Address union all
select ''PhoneNumber_type'', PhoneNumber_type, PhoneNumber
) c(orig, col, value)
) src
pivot
(
max(value)
for col in (' + @cols + N')
) p '
exec sp_executesql @query;
这是另一个演示.
这篇关于在MySQL中将行记录转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
