MySQL版本
您已经声明过尝试使用PIVOT,但是MySQL没有PIVOT函数.在MySQL中,您需要将聚合函数与条件逻辑语句(如CASE … WHEN或类似的东西)一起使用.您还需要旋转几个表和几个不同的列,这使事情变得有些复杂.似乎您还需要创建数量未知的新列,这增加了另一层复杂性.
如果您知道要在最终结果中显示的所有列,则可以轻松键入此查询的版本,如下所示：
select
p1.`SSN_ID`,
p1.Citizen_name,
pany,
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,
pany;
基本上,您在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,
pany, ', @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,
pany');
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,
pany, ', @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,
pany');
#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,
pany,
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,
pany,
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,
pany,
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.
