quotename mysql_在MySQL中将行记录转换为列

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.