SQL动态查询示例-优快云博客

本文介绍了一种使用T-SQL动态构建复杂查询的方法，通过示例展示了如何为不同的时间点选择最大日期值，并以特定格式展示结果。此方法适用于需要灵活生成报表或其他需要动态聚合数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE TABLE ##test (projectname varchar(10),[TIME] datetime)
INSERT INTO ##test
SELECT '四门子', '2012-07-04'
UNION ALL
SELECT '四门子', '2012-08-21'
UNION ALL
SELECT '四门子', '2012-09-30'
UNION ALL
SELECT '来福', '2012-07-04'
UNION ALL
SELECT '来福', '2012-08-21'
UNION ALL
SELECT '来福', '2012-09-30'
UNION ALL
SELECT '来福', '2012-10-21'
UNION ALL
SELECT '来福', '2012-10-30'

declare @s nvarchar(4000)
set @s=''

Select @s=@s+','+quotename(time)+'=max(case when [time]='+quotename([time],'''')+' then [time] else null end)'
from ##test group BY time
print('select projectname'+@s+' from ##test group by projectname')
exec('select projectname'+@s+' from ##test group by projectname')
--print出来的
select projectname,[Jul 4 2012 12:00AM]=max(case when [time]='Jul 4 2012 12:00AM' then [time] else null end),
[Aug 21 2012 12:00AM]=max(case when [time]='Aug 21 2012 12:00AM' then [time] else null end),
[Sep 30 2012 12:00AM]=max(case when [time]='Sep 30 2012 12:00AM' then [time] else null end),
[Oct 21 2012 12:00AM]=max(case when [time]='Oct 21 2012 12:00AM' then [time] else null end),
[Oct 30 2012 12:00AM]=max(case when [time]='Oct 30 2012 12:00AM' then [time] else null end)
from test group by projectname
-- drop table ##test
/*
来福 2012-07-04 00:00:00.000 2012-08-21 00:00:00.000 2012-09-30 00:00:00.000 2012-10-21 00:00:00.000 2012-10-30 00:00:00.000
四门子 2012-07-04 00:00:00.000 2012-08-21 00:00:00.000 2012-09-30 00:00:00.000 NULL NULL
*/