问个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-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请高手帮帮忙看怎么才能实现啊?多谢多谢!
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 Date1 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)
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
*/
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'
go
declare @s varchar(8000)
set @s='select DepartName'
select @s=@s+',max(case bh when '''+rtrim(bh)+''' then '''+convert(varchar(10),Date1,120)+''' else null end) as 列'+rtrim(bh)+''
from (select *,bh=(select count(1) from tt where DepartName=b1.DepartName and Date1<=b1.Date1) from tt b1)t
group by bh,Date1--select @s
select @s=@s+'
from (select *,bh=(select count(1) from tt where DepartName=b1.DepartName and Date1<=b1.Date1) from tt b1)t
group by DepartName'
--select @s
exec(@s)drop table ttDepartName 列1 列2 列3 列4
---------- ---------- ---------- ---------- ----------
001 2007-05-01 2007-05-02 2007-05-03 2007-05-04警告: 聚合或其它 SET 操作消除了空值。
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)