22,207
社区成员
发帖
与我相关
我的任务
分享
create function f_place(@place varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + [到达地] from tb where [车牌号] = @place
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + [车牌号] from (SELECT [日期] = CONVERT(VARCHAR(10),[日期],120),[车牌号],[到达地] = dbo.f_place([车牌号]) FROM TB )TT GROUP BY [车牌号]
set @sql = '[' + @sql + ']'
--PRINT @sql
exec ('select * from (select * from (SELECT [日期] = CONVERT(VARCHAR(10),[日期],120),[车牌号],[到达地] = dbo.f_place([车牌号]) FROM TB )TT) a pivot (max([到达地]) for [车牌号] in (' + @sql + ')) b')
/*
日期 05483 07110 08123
2010-01-01 安徽,江苏 上海 杭州
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([日期] datetime,[车牌号] varchar(5),[到达地] varchar(4))
insert [tb]
select '2010/01/01','05483','安徽' union all
select '2010/01/01','05483','江苏' union all
select '2010/01/01','07110','上海' union all
select '2010/01/01','08123','杭州'
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'max(case when 车牌号='''+车牌号+''' then 到达地 else '''' end) as ['+车牌号+']'
from
(select distinct 车牌号 from tb) t
exec ('select convert(varchar(10),日期,111) as 日期,'
+@sql
+' from tb group by convert(varchar(10),日期,111)'
)
---结果---
日期 05483 07110 08123
---------- ----- ----- -----
2010/01/01 江苏 上海 杭州
(1 行受影响)