我正在尝试将行数据转换为列.数据将从不同的表中提取.我尝试过使用PIVOT,但效果不佳.
让我们将列1作为每个表中的主键.
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或类似的东西)一起使用.您还需要旋转几个表和几个不同的列,这使事情变得有些复杂.似乎您还需要创建数量未知的新列,这增加了另一层复杂性.
如果您知道要在最终结果中显示的所有列,则可以轻松键入此查询的版本,如下所示：
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.您的代码看起来像这样：
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上创建了一个演示以显示代码,这两者都会产生结果：
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
| 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 |
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
根据您的评论(每个人可能有多个电话号码类型),您需要创建row number for each group of phone types.不幸的是,MySQL又没有窗口功能,因此您需要使用用户定义的变量来获取最终的结果.当查询PhoneNumber_type时,您将需要使用类似以下的内容：
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代码中：
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;
参见另一个demo.
SQL Server版本
既然您已经说过需要SQL Server版本,那么这里就是该版本. SQL Server具有一些使操作变得更加容易的功能,包括PIVOT函数,UNPIVOT函数以及诸如row_number之类的窗口函数.这是查询的静态版本,其中只列出了几列：
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版本,则代码如下所示：
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;
这是另一个demo.