当前位置: 代码迷 >> Sql Server >> 关于动态SQL有关问题
  详细解决方案

关于动态SQL有关问题

热度:63   发布时间:2016-04-27 20:53:48.0
关于动态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 @[email protected]+ ' 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 @[email protected]+ ',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 @[email protected]+ ' 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)
  相关解决方案