34,588
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([date_time] Date,[user_id] nvarchar(24),[money] decimal(18,8))
Insert #T
select '20160104',N'0002',95.32 union all
select '20160105',N'0001',44.6 union all
select '20160105',N'0002',100.3 union all
select '20160105',N'0003',100.3
Go
--测试数据结束
DECLARE @sql NVARCHAR(4000);
SET @sql = 'select DATEADD(DAY,number,''2016-01-01'') AS [date_time]';
SELECT @sql = @sql + ',max(case when [user_id] = ''' + RTRIM([user_id])
+ ''' then [money] else null end ) as [' + RTRIM([user_id]) + ']'
FROM ( SELECT DISTINCT
[user_id]
FROM #T
) a;
SET @sql = @sql
+ ' from master..spt_values LEFT JOIN #T ON DATEADD(DAY,number,''2016-01-01'') = [date_time]
WHERE type = ''P'' group by DATEADD(DAY,number,''2016-01-01'')';
EXEC(@sql);