SQL Server合并重复行与行列转换（附实例）

优化后的需求：请按照月份查询每个员工该月每天的打卡记录（如下图所示），早上与晚上通过分隔符“||”分割（其实本来的需求是要求回车的，但是我没找到在数据库进行回车的好办法，协商后改成了||，如果看到这篇文章的人知道如何进行分割请告诉我。

表设计：（该需求是2月份因为疫情提出的后加功能，而一开始的打卡表设计是一张灵活的简单表，导致了复杂行列转换的产生）

分析：由表中数据可得，1名员工上下班打两次卡，会产生两条数据。需要将同一天的数据首先进行合并，然后将日期进行行列转换变成表头字段。

1、将同天数据合并（不想看的可以直接看2行列转换）：

      a).采用for xml path。for xml path较为详细的用法：点击进入

      b)通过stuff将合并列中的<xxx>去除，日期之间用“||”分割。较为详细的用法：点击进入

我的代码如下：

select pro_dcp_person,(

---进行字符分割
 select stuff((

    --进行重复行合并
     select CONVERT(varchar(20), pro_dcp_date,24)+'||' from pro_dailypunchcard 
     where 1=1 
     and CONVERT(varchar(10), pro_dcp_date, 120) = CONVERT(varchar(10),dcp.pro_dcp_date, 120)
     and pro_dcp_person = dcp.pro_dcp_person for xml path('')
    --合并结束

 ),1,0,'')
---字符分割结束


 )as alldate,CONVERT(varchar(10),dcp.pro_dcp_date, 120)as pro_dcp_date from pro_dailypunchcard dcp 
 where 1=1
 and CONVERT(varchar(10), pro_dcp_date, 120) in ('2020-04-01','2020-04-02','2020-04-03')
 group by pro_dcp_person,CONVERT(varchar(10),dcp.pro_dcp_date, 120)

查询结果如下：

 

2、对如上图所示的查询结果进行直接的行列转换我没有直接成功。

case when 失败代码（复盘的时候懒得再加并没有用的max()了）：

select a.*,
case when CONVERT(varchar(10), a.pro_dcp_date, 120) = '2020-04-01' then a.alldate else '/' end as '2020-04-01',
case when CONVERT(varchar(10), a.pro_dcp_date, 120) = '2020-04-02' then a.alldate else '/' end as '2020-04-02',
case when CONVERT(varchar(10), a.pro_dcp_date, 120) = '2020-04-03' then a.alldate else '/' end as '2020-04-03'

 from (

 select pro_dcp_person,(

 select stuff((
 select CONVERT(varchar(20), pro_dcp_date,24)+'||' from pro_dailypunchcard 
 where 1=1 
 and CONVERT(varchar(10), pro_dcp_date, 120) = CONVERT(varchar(10),dcp.pro_dcp_date, 120)
 and pro_dcp_person = dcp.pro_dcp_person for xml path('')
 ),1,0,'')

 )as alldate,CONVERT(varchar(10),dcp.pro_dcp_date, 120)as pro_dcp_date from pro_dailypunchcard dcp 
 
 
 
 where 1=1
 and CONVERT(varchar(10), pro_dcp_date, 120) in ('2020-04-01','2020-04-02','2020-04-03')
 group by pro_dcp_person,CONVERT(varchar(10),dcp.pro_dcp_date, 120)
 
 
 ) as a

case when 失败结果：

↑4月1日至4月3日之间，1079用户的打卡数据应该只有一条，在case when中却出现了三条，合并未成功。

做到这里其实可以把数据传回去在impl层进行数据拆解合并。我最后差一点点要放弃在数据库一步到位了，然后公司年资较长的工程师给我推荐了pivot。

超详细pivot教程：点击进入 pivot有一点点限制，但是对于较为互联网的企业来说不是问题。

但是对于我而言问题并不能直接使用该教程中的存储过程。这篇文章主要也是记录在这种需要对表中数据进行处理，处理之后再行转列的情形。

a)将已经处理的数据转换成视图。

b)在视图中进行行列转换。

因此我需要将我第1步中处理好的数据转换成视图。

转换代码如下：

create VIEW viw_dailypunchcard 
AS 

select pro_dcp_person,(

 select stuff((
 select CONVERT(varchar(20), pro_dcp_date,24)+CHAR(13) from pro_dailypunchcard 
 where 1=1 
 and CONVERT(varchar(10), pro_dcp_date, 120) = CONVERT(varchar(10),dcp.pro_dcp_date, 120)
 and pro_dcp_person = dcp.pro_dcp_person for xml path('')
 ),1,0,'')

 )as alldate,CONVERT(varchar(10),dcp.pro_dcp_date, 120)as pro_dcp_date from pro_dailypunchcard dcp 
 
 
 
 where 1=1
 --and CONVERT(varchar(10), pro_dcp_date, 120) in ('2020-03-01','2020-03-02','2020-03-03')
 group by pro_dcp_person,CONVERT(varchar(10),dcp.pro_dcp_date, 120);

然后在存储过程中进行行列转换，转换代码如下：

DECLARE @dec_val NVARCHAR(MAX)
DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表
DECLARE @groupColumn SYSNAME --分组字段
DECLARE @row2column SYSNAME --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = 'viw_dailypunchcard' --视图名称
SET @groupColumn = 'pro_dcp_person'
SET @row2column = 'pro_dcp_date'
SET @row2columnValue = 'alldate'

--声明要传入的日期
SET @dec_val='''2020-04-01'',''2020-04-02'',''2020-04-03''';

--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+']) 
    FROM ['+@tableName+'] where CONVERT(varchar(10), ['+@row2column+'], 120) in ('+@dec_val+') GROUP BY ['+@row2column+'] ORDER BY ['+@row2column+'] ASC  '
PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
PRINT @sql_col

SET @sql_str = N'
select a.*,sysp_use_name from (

SELECT * FROM (
    SELECT [pro_dcp_person],[pro_dcp_date],[alldate] FROM [viw_dailypunchcard]) p 
    PIVOT 
    (MAX([alldate]) FOR [pro_dcp_date] IN ( '+ @sql_col+') ) AS pvt
) as a
--自我添加的关联表部分
left join (select * from sysp_user where sysp_use_valid = ''1'') u
on u.sysp_use_id = a.[pro_dcp_person]

ORDER BY a.[pro_dcp_person]
'
PRINT (@sql_str)
EXEC (@sql_str)

输出结果：

行列转换结束……

 

……整了我快六个小时的。一气之下都准备在impl层做数据拆解处理了。

总之方法总比困难多（。