Questions about turn essentially a line of columns-CodePudding

Purpose is to change the table structure such as

The final query result if such (date is not fixed, may be arbitrary date)

Is essentially a version 2014, use the pivot to turn line column also not line, the result of the need is not the sort of statistics, the number of forms, such as which bosses will write, save the children,

posted below table and data created by the insert SQL

 CREATE TABLE [dbo] [T_OnDuty] (
[OndutyId] [uniqueidentifier] NULL, 
[the DeptName] [varchar] (50) NULL, 
[EmpName] [varchar] (50) NULL, 
[EmpId] [varchar] (50) NULL, 
[OndutyDate] [date] NULL, 
[CreateTime] [datetime] NULL, 
[Creator] [varchar] (50) NULL 
) ON the (PRIMARY) 
GO 
INSERT [dbo] [T_OnDuty] ([OndutyId], [the DeptName], [EmpName], [EmpId], [OndutyDate], [CreateTime], [Creator]) VALUES (N '3 cdd4344 - c380-43 fb - 834 - c - 48 cfde5dfb08', N 'engineering', N 'zhangsan', N 'M001' CAST (N '2020-10-01' AS the Date), CAST (N '2020-09-26 T23:37:07. 617' AS a DateTime), N ') 
INSERT [dbo] [T_OnDuty] ([OndutyId], [the DeptName], [EmpName], [EmpId], [OndutyDate], [CreateTime], [Creator]) VALUES (N 'a83 c0017d c9d8-63-4-8 bb3 a0f - 042464454', N 'engineering', N 'zhangsan', N 'M001' CAST (N '2020-10-02' AS the Date), CAST (N '2020-09-26 T23: themselves. 597' AS a DateTime), N ') 
INSERT [dbo] [T_OnDuty] ([OndutyId], [the DeptName], [EmpName], [EmpId], [OndutyDate], [CreateTime], [Creator]) VALUES (N 'dc5 b360c3d 2-5-4974 - d1a9b4f3 a1e8-9050', N 'engineering', N 'zhao a' N 'M002' CAST (N '2020-10-05' AS the Date), CAST (N '2020-09-26 T23: those. 847' AS a DateTime), N ') 
INSERT [dbo] [T_OnDuty] ([OndutyId], [the DeptName], [EmpName], [EmpId], [OndutyDate], [CreateTime], [Creator]) VALUES (N 'f00e1614 - e464-4033-9 c26 - ece99259a73a', N 'finance', N 'bill', N 'C001' CAST (N '2020-10-01' AS the Date), CAST (N '2020-09-26 T23:40:59. 240' AS a DateTime), N ') 
INSERT [dbo] [T_OnDuty] ([OndutyId], [the DeptName], [EmpName], [EmpId], [OndutyDate], [CreateTime], [Creator]) VALUES (N '72 b42 b3af44-5575-4-8 ed7-58 a2bfc0d4fa', N 'finance', N 'bill', N 'C001' CAST (N '2020-10-02' AS the Date), CAST (N '2020-09-26 T23:41:02. 823' AS a DateTime), N ') 
INSERT [dbo] [T_OnDuty] ([OndutyId], [the DeptName], [EmpName], [EmpId], [OndutyDate], [CreateTime], [Creator]) VALUES (N 'b9e7d3de - 14 b6-4709 - bd51 - eb6244efa5ea', N 'finance', N 'Cathy', N 'C002' CAST (N '2020-10-02' AS the Date), CAST (N '2020-09-26 T23: unto. 103' AS a DateTime), N ')

CodePudding user response:

E.g.

 DECLARE @ Sql NVARCHAR (4000)=N '; 
SELECT Sql +=N '@, Max (case when [OndutyDate]=' + QUOTENAME ([OndutyDate], ' ' ' ') 
+ N 'then EmpName else' ' ' 'end) AS' + QUOTENAME ([OndutyDate]) 
The FROM [T_OnDuty] 
GROUP BY [OndutyDate]; 

The SET @ Sql='[the DeptName] 
 SELECT a.'+ @ Sql +' 
The FROM (SELECT a. [the DeptName] 
, a. [OndutyDate] 
That STUFF (b.E mpName, 1, 1, ' ' ' ') AS EmpName 
The FROM (SELECT DISTINCT [the DeptName], [OndutyDate] FROM [T_OnDuty]) AS a CROSS the APPLY (SELECT "', '+ [EmpName] 
The FROM [T_OnDuty] 
WHERE [the DeptName]=a. [the DeptName] 
[OndutyDate] AND [OndutyDate]=a. 
FOR XML PATH (" ', ')) b ([EmpName])) as a 
GROUP BY a. [the DeptName] 
; '
- look at generating statement format 
PRINT @ Sql 
The EXEC (@ Sql); 

; 
/* 
The DeptName 2020-10-01 2020-10-02 2020-10-01 
Finance department li si li si, fifty 
The engineering zhangsan zhangsan zhao a 
*/

CodePudding user response:

Pivot with aggregate function is to use, you this is merge field value, do not apply