关于动态SQL问题
问个SQL问题，我现在有个部门日清表 table1，里面结构设计 ：部门代码+日期为主键，现在存完以后的效果是

DepartName Date1
001 2007-5-1
001 2007-5-2
001 2007-5-3
001 2007-5-4

但在前台的显示效果我却想要

001 2007-5-1 2007-5-2 2007-5-3 2007-5-4

请高手帮帮忙看怎么才能实现啊？多谢多谢！

[解决办法]
001 2007-05-03 00:00:00.000 2007-05-03 00:00:00.000 2007-05-03 00:00:00.000

[解决办法]
create table table1(DepartName nvarchar(10),Date1 datetime )
insert into table1
select
'001 ', '2007-5-1 '
union select
'001 ', '2007-5-2 '
union select
'001 ', '2007-5-3 '
union select
'001 ', '2007-5-4 '


declare @sql nvarchar(4000)
set @sql= ' '
select @sql=@sql+ ' max(case date1 when ' ' '+date1+ ' ' ' then date1 end ) as ' ' ' + date1+ ' ' ' , '
from (
select distinct Convert(varchar(10),date1,120) as date1 from table1
)a
select @sql= 'select DepartName, '+ left(@sql,len(@sql)-1)+ 'from table1 group by DepartName '
print @sql
exec(@sql)


－－结果
001 2007-05-01 00:00:00.000 2007-05-02 00:00:00.000 2007-05-03 00:00:00.000 2007-05-04 00:00:00.000

老生常谈的问题，行转列



[解决办法]
--只示日期，去掉,借上代一用
create table tt(DepartName varchar(10),Date1 datetime)
insert tt select '001 ', '2007-5-1 '
union all select '001 ', '2007-5-2 '
union all select '001 ', '2007-5-3 '
union all select '001 ', '2007-5-4 '

declare @s varchar(8000)
set @s= 'select DepartName '
select @s=@s+ ',max(case DepartName when ' ' '+DepartName+ ' ' ' then convert(varchar(10),Date1,120) else null end)as [ '+convert(varchar(10),Date1,120)+ '] '
from tt group by DepartName,Date1
select @s=@s+ ' from tt group by DepartName '
--select @s
exec(@s)

/*
果：
DepartName 2007-05-01 2007-05-02 2007-05-03 2007-05-04
---------- ---------- ---------- ---------- ----------
001 2007-05-04 2007-05-04 2007-05-04 2007-05-04
*/
[解决办法]
DECLARE @SqlStr NVARCHAR(4000)
SET @SqlStr = 'SELECT DepartName '
SELECT @SqlStr = @SqlStr + ', ' + 'MAX(CASE date1 WHEN ' ' '+date1+ ' ' ' THEN date1 END) AS ' ' ' + date1 + ' ' ' '
FROM (SELECT DISTINCT Convert(NVARCHAR(10),date1,120) AS date1 FROM table1) A
SELECT @SqlStr = @SqlStr + ' from table1 group by DepartName '

EXEC(@SqlStr)