34,587
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[tb1](
[编号] [int] IDENTITY(1,1) NOT NULL,
[部门] [nvarchar](50) NULL,
[数量] [int] NULL,
[新增时间] [date] NULL,
[提交时间] [date] NULL,
CONSTRAINT [PK_tb1] PRIMARY KEY CLUSTERED
(
[编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tb1] ON
GO
INSERT [dbo].[tb1] ([编号], [部门], [数量], [新增时间], [提交时间]) VALUES (1, N'F1', 1, CAST(N'2018-03-04' AS Date), CAST(N'2018-03-04' AS Date))
GO
INSERT [dbo].[tb1] ([编号], [部门], [数量], [新增时间], [提交时间]) VALUES (2, N'F1', 4, CAST(N'2018-03-04' AS Date), CAST(N'2018-03-05' AS Date))
GO
INSERT [dbo].[tb1] ([编号], [部门], [数量], [新增时间], [提交时间]) VALUES (3, N'F2', 3, CAST(N'2018-03-05' AS Date), CAST(N'2018-03-05' AS Date))
GO
SET IDENTITY_INSERT [dbo].[tb1] OFF
GO
DECLARE @sql VARCHAR(8000)
SET @sql = 'select 部门'
SELECT @sql = @sql
+ CASE WHEN 新增时间 IS NOT NULL
THEN ' , sum(case 新增时间 when ''' + RTRIM(ISNULL(新增时间, ''))
+ ''' then 数量 else 0 end) [' + RTRIM(ISNULL(新增时间, ''))
+ '新增] '
ELSE ''
END + CASE WHEN 提交时间 IS NOT NULL
THEN '
, sum(case 提交时间 when ''' + RTRIM(ISNULL(提交时间, ''))
+ ''' then 数量 else 0 end) [' + RTRIM(ISNULL(提交时间, ''))
+ '提交]'
ELSE ''
END
FROM ( SELECT *
FROM ( SELECT DISTINCT
新增时间
FROM tb1
WHERE 新增时间 IS NOT NULL
) t1
FULL JOIN ( SELECT DISTINCT
提交时间
FROM tb1
WHERE 提交时间 IS NOT NULL
) t2 ON t1.新增时间 = t2.提交时间
) AS a
SET @sql = @sql + ' from tb1 group by 部门'
EXEC(@sql)
declare @sql varchar(8000)
set @sql='select 部门'
select @sql=@sql+' , sum(case 新增时间 when '''+ RTRIM(新增时间) +''' then 数量 else 0 end) ['+ RTRIM(新增时间) +'新增]
, sum(case 提交时间 when '''+ RTRIM(提交时间) +''' then 数量 else 0 end) ['+ RTRIM(提交时间) +'提交]'
from (
SELECT * FROM (select distinct 新增时间 from tb1 WHERE 新增时间 IS NOT NULL)t1
FULL JOIN (select distinct 提交时间 from tb1 WHERE 提交时间 IS NOT NULL)t2 ON t1.新增时间=t2.提交时间
) as a
set @sql=@sql+' from tb1 group by 部门'
EXEC(@sql)
[/quote]declare @sql varchar(8000)
set @sql='select 部门'
select @sql=@sql+' , sum(case 新增时间 when '''+ RTRIM(新增时间) +''' then 数量 else 0 end) ['+ RTRIM(新增时间) +'新增]
, sum(case 提交时间 when '''+ RTRIM(提交时间) +''' then 数量 else 0 end) ['+ RTRIM(提交时间) +'提交]'
from (
SELECT * FROM (select distinct 新增时间 from tb1 WHERE 新增时间 IS NOT NULL)t1
FULL JOIN (select distinct 提交时间 from tb1 WHERE 提交时间 IS NOT NULL)t2 ON t1.新增时间=t2.提交时间
) as a
set @sql=@sql+' from tb1 group by 部门'
EXEC(@sql)
declare @sql varchar(8000)
set @sql='select 部门'
select @sql=@sql+' , sum(case 新增时间 when '''+ RTRIM(新增时间) +''' then 数量 else 0 end) ['+ RTRIM(新增时间) +'新增]
, sum(case 提交时间 when '''+ RTRIM(提交时间) +''' then 数量 else 0 end) ['+ RTRIM(提交时间) +'提交]'
from (
SELECT * FROM (select distinct 新增时间 from tb1)t1
FULL JOIN (select distinct 提交时间 from tb1)t2 ON t1.新增时间=t2.提交时间
) as a
set @sql=@sql+' from tb1 group by 部门'
EXEC(@sql)