关于动态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)
