34,576
社区成员
发帖
与我相关
我的任务
分享
use tempdb
create table tab(编号 int identity(1,1),日期 date,姓名 Nvarchar(100),客户数 int)
insert into tab
select '2015-05-18 00:00:00.000', '张三', 30 union all
select '2015-05-18 00:00:00.000', '李四', 2 union all
select '2015-05-18 00:00:00.000', '王五', 10 union all
select '2015-05-22 00:00:00.000', '张三', 30 union all
select '2015-05-22 00:00:00.000', '李四', 2 union all
select '2015-05-22 00:00:00.000', '王五', 10
go
declare @sql Nvarchar(max)
select @sql=isnull(@sql+',','')+QUOTENAME(convert(varchar(10),日期,120),']')
from tab
where 日期 between '20150510' and '20150530'
group by convert(varchar(10),日期,120)
set @sql='select *
from (select convert(varchar(10),日期,120) 日期,姓名,客户数 from tab)
a pivot (sum(客户数) for 日期 in('+@sql+'))p'
--print @sql
exec(@sql)
--动态
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT 姓名'
SELECT @SQL=@SQL+',SUM(CASE WHEN CONVERT(VARCHAR(10),日期,120)='''+日期+''' THEN 客户数 END)['+日期+']'
FROM(SELECT CONVERT(VARCHAR(10),日期,120)日期 FROM TB GROUP BY CONVERT(VARCHAR(10),日期,120))T
ORDER BY 日期
SET @SQL=@SQL+' FROM TB GROUP BY 姓名'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT 姓名
,SUM(CASE WHEN CONVERT(VARCHAR(10),日期,120)='2015-05-18' THEN 客户数 END)[2015-05-18]
,SUM(CASE WHEN CONVERT(VARCHAR(10),日期,120)='2015-05-22' THEN 客户数 END)[2015-05-22]
FROM TB
GROUP BY 姓名
with cte(编号,日期,姓名,客户数)
as
(
select 1,'2015-05-18 00:00:00.000',N'张三',30 union all
select 2,'2015-05-18 00:00:00.000',N'李四',2 union all
select 3,'2015-05-18 00:00:00.000',N'王五',10 union all
select 4,'2015-05-22 00:00:00.000',N'张三',30 union all
select 5,'2015-05-22 00:00:00.000',N'李四',2 union all
select 6,'2015-05-22 00:00:00.000',N'王五',10
)
select 姓名,sum(case when convert(varchar(10),日期)='2015-05-18' then 客户数 end) as [2015-05-18],sum(case when convert(varchar(10),日期)='2015-05-22' then 客户数 end) as [2015-05-22] from cte
group by 姓名