--测试数据
if not object_id(N'A') is null
drop table A
Go
Create table A([name] nvarchar(22),[date] nvarchar(25),[bc] nvarchar(22),[jg] nvarchar(22))
Insert A
select N'张三',N'11月1日',N'上班',N'正常' union all
select N'张三',N'11月1日',N'下班',N'正常' union all
select N'张三',N'11月2日',N'上班',N'正常' union all
select N'张三',N'11月2日',N'下班',N'正常'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select Name'
SELECT @sql = @sql + ',max(case RTRIM(date)+bc when ''' + aa
+ ''' then jg else null end)[' + aa+ ']'
FROM ( SELECT DISTINCT
RTRIM(date)+bc AS aa
FROM A
) t
SET @sql = @sql
+ ' from A group by Name'
EXEC(@sql)
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[name] NVARCHAR(MAX)
,[date] NVARCHAR(MAX)
,[bc] NVARCHAR(MAX)
,[jg] NVARCHAR(MAX)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'张三',N'11月1日',N'上班',N'正常')
INSERT INTO dbo.[t] VALUES(N'张三',N'11月1日',N'下班',N'正常')
INSERT INTO dbo.[t] VALUES(N'张三',N'11月2日',N'上班',N'正常')
INSERT INTO dbo.[t] VALUES(N'张三',N'11月2日',N'下班',N'正常')
INSERT INTO dbo.[t] VALUES(N'李四',N'11月3日',N'下班',N'正常')
GO
--以上为测试数据， 加了一行 “李四”
DECLARE @sql NVARCHAR(MAX)
SELECT @sql='
SELECT [name]'+(
SELECT ',(SELECT TOP 1 jg FROM t AS b WHERE a.[name]=b.[name] AND b.[date]='''+[date]+''' AND b.bc=''上班'') AS ['+[date]+'上班],(SELECT TOP 1 jg FROM t AS b WHERE a.[name]=b.[name] AND b.[date]='''+[date]+''' AND b.bc=''下班'') AS ['+[date]+'下班]'
FROM t GROUP BY [date]
FOR XML PATH(''))
+'
FROM t AS a
GROUP BY [name]'
PRINT @sql
EXEC (@sql)
--测试数据
if not object_id(N'A') is null
drop table A
Go
Create table A([name] nvarchar(22),[date] nvarchar(25),[bc] nvarchar(22),[jg] nvarchar(22))
Insert A
select N'张三',N'2018-11-01',N'上班',N'正常' union all
select N'张三',N'2018-11-01',N'下班',N'正常' union all
select N'张三',N'2018-11-02',N'上班',N'正常' union all
select N'张三',N'2018-11-02',N'下班',N'正常'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select Name'
SELECT @sql = @sql + ',max(case RTRIM(date)+bc when ''' + aa
+ ''' then jg else null end)[' + aa+ ']'
FROM ( SELECT DISTINCT RTRIM(day)+a aa FROM (
SELECT convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%')t CROSS APPLY(VALUES('上班'),('下班'))ttt(a)
) t
SET @sql = @sql
+ ' from A group by Name'
EXEC(@sql)
--测试数据
IF NOT OBJECT_ID(N'TEMPDB.DBO.#T') IS NULL
DROP TABLE #T
GO
CREATE TABLE #T(NAME NVARCHAR(50),[DATE] NVARCHAR(20),BC NVARCHAR(20),JG NVARCHAR(20))
INSERT #T
SELECT N'张三',N'2018-11-01',N'上班',N'正常' UNION all
SELECT N'张三',N'2018-11-01',N'下班',N'正常' UNION all
SELECT N'张三',N'2018-11-02',N'上班',N'正常' UNION all
SELECT N'张三',N'2018-11-02',N'下班',N'正常' UNION ALL
SELECT N'李四',N'2018-11-02',N'下班',N'正常'
GO
DECLARE @MONTH VARCHAR(10)
DECLARE @SQL VARCHAR(8000)
SET @MONTH='2018-11'
;WITH CTE
AS
(SELECT DATEADD(DAY,NUMBER,CAST(@MONTH+'-01' AS DATE)) AS PER_DATE, C.TYPE
FROM MASTER.DBO.SPT_VALUES A
JOIN (SELECT DATEDIFF(DAY,CAST(@MONTH+'-01' AS DATE),DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@MONTH+'-01' AS DATE)))) AS DIFF) AS B
ON A.NUMBER<=B.DIFF
JOIN (SELECT '上班' AS TYPE UNION SELECT '下班') AS C ON 1=1
WHERE A.TYPE='P')
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN [DATE]='''+CAST(PER_DATE AS VARCHAR(20))+''' AND BC='''+TYPE+''' THEN JG END) AS '''+ CAST(PER_DATE AS VARCHAR)+TYPE+''''
FROM (SELECT DISTINCT PER_DATE,TYPE FROM CTE) AS A
SET @SQL='DECLARE @MONTH VARCHAR(10)
SET @MONTH=''2018-11''
;WITH CTE
AS
(SELECT DATEADD(DAY,NUMBER,CAST(@MONTH+''-01'' AS DATE)) AS PER_DATE, C.TYPE,D.NAME
FROM MASTER.DBO.SPT_VALUES A
JOIN (SELECT DATEDIFF(DAY,CAST(@MONTH+''-01'' AS DATE),DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@MONTH+''-01'' AS DATE)))) AS DIFF) AS B
ON A.NUMBER<=B.DIFF
JOIN (SELECT ''上班'' AS TYPE UNION SELECT ''下班'') AS C ON 1=1
JOIN (SELECT DISTINCT NAME FROM #T) AS D ON 1=1
WHERE A.TYPE=''P'')
SELECT A.NAME,'+@SQL+'
FROM CTE A
LEFT JOIN #T B ON A.NAME=B.NAME AND A.TYPE=B.BC AND A.PER_DATE=B.DATE
GROUP BY A.NAME'
EXEC(@SQL)
