挑战一sql，一直没有头绪，挺复杂的行列转换，请大家多多指教，等候中.

[DoNotTomcat]

表(mytest)数据如下

id uname dept time1 time2
1 a Q1 2007-10-01 2007-10-02
2 b Q1 2007-10-08 2007-10-09
3 c Q1 2007-10-15 2007-10-16
4 d Q1 2007-10-22 2007-10-23
5 a Q2 2007-10-03 2007-10-04
6 b Q2 2007-10-10 2007-10-11
7 c Q2 2007-10-17 2007-10-18
8 d Q2 2007-10-24 2007-10-25
...........

以上的数据是有规律，如我给出的所示。

最终结果

uname Q1 Q2
a 10.01-10.02 10.03-10.04
b 10.08-10.09 10.10-10.11
c 10.15-10.16 10.24-10.18
d 10.22-10.23 10.24-10.25

以uname进行分组，
根据uname可分四组(从a到d)，dept为Q1和Q2，所以两列的值，列名为Q1和Q2，值分别为time1和time2的日期格式化

不知如何写出这个动态的sql，请指教。

[-狙击手-]

create table mytest ([id] int,uname char(2),dept varchar(2),time1 datetime,time2 datetime)
insert mytest select 1,'a','Q1','2007-10-01','2007-10-02'   
insert mytest select 2,'b','Q1','2007-10-08','2007-10-09'   
insert mytest select 3,'c','Q1','2007-10-15','2007-10-16'   
insert mytest select 4,'d','Q1','2007-10-22','2007-10-23'   
insert mytest select 5,'a','Q2','2007-10-03','2007-10-04'   
insert mytest select 6,'b','Q2','2007-10-10','2007-10-11'   
insert mytest select 7,'c','Q2','2007-10-17','2007-10-18'   
insert mytest select 8,'d','Q2','2007-10-24','2007-10-25'

declare @sql varchar(8000)

set @sql = 'select uname '
select @sql = @sql+',['+dept+']=max(case when dept = '''+dept+''' then utime1 else null end) '
from (select distinct dept from mytest) a
exec( @sql+' from (select uname,dept,
utime1= right(convert(varchar(10),time1,102),5)+''-''+right(convert(varchar(10),time2,102),5)
from mytest group by uname,dept,right(convert(varchar(10),time1,102),5)+''-''+right(convert(varchar(10),time2,102),5)) a group by uname')
drop table mytest

/*
uname Q1                    Q2                    
----- --------------------- --------------------- 
a     10.01-10.02           10.03-10.04
b     10.08-10.09           10.10-10.11
c     10.15-10.16           10.17-10.18
d     10.22-10.23           10.24-10.25

*/

[wzy_love_sly]

declare @tb table (id int,uname varchar(10),dept varchar(10),time1 datetime,time2 datetime)
insert @tb select 1,'a','Q1','2007-10-01','2007-10-02'
insert @tb select 2 ,'b','Q1','2007-10-08','2007-10-09' 
insert @tb select 3 ,'c','Q1','2007-10-15','2007-10-16'
insert @tb select 4 ,'d','Q1','2007-10-22','2007-10-23'
insert @tb select 5 ,'a','Q2','2007-10-03','2007-10-04' 
insert @tb select 6 ,'b','Q2','2007-10-10','2007-10-11' 
insert @tb select 7 ,'c','Q2','2007-10-17','2007-10-18' 
insert @tb select 8 ,'d','Q2','2007-10-24','2007-10-25' 

select uname,
max(case dept when 'q1' then right(convert(varchar(10),time1,102),5)+'-'+right(convert(varchar(10),time2,102),5) end )q1,
max(case dept when 'q2' then right(convert(varchar(10),time1,102),5)+'-'+right(convert(varchar(10),time2,102),5) end)q2
from @tb group by uname

[wzy_love_sly]

a 10.01-10.02 10.03-10.04
b 10.08-10.09 10.10-10.11
c 10.15-10.16 10.17-10.18
d 10.22-10.23 10.24-10.25

[dawugui]

[code=SQL]create table tb(id int,uname varchar(10),dept varchar(10),time1 datetime,time2 datetime)
insert into tb values(1,         'a',         'Q1',         '2007-10-01',         '2007-10-02')   
insert into tb values(2,         'b',         'Q1',         '2007-10-08',         '2007-10-09')   
insert into tb values(3,         'c',         'Q1',         '2007-10-15',         '2007-10-16')   
insert into tb values(4,         'd',         'Q1',         '2007-10-22',         '2007-10-23')   
insert into tb values(5,         'a',         'Q2',         '2007-10-03',         '2007-10-04')   
insert into tb values(6,         'b',         'Q2',         '2007-10-10',         '2007-10-11')   
insert into tb values(7,         'c',         'Q2',         '2007-10-17',         '2007-10-18')   
insert into tb values(8,         'd',         'Q2',         '2007-10-24',         '2007-10-25')   
go
--静态SQL,指dept只有Q1,Q2两种．
select uname , 
  max(case dept when 'Q1' then right(convert(varchar(10),time1,102),5) + '-' + right(convert(varchar(10),time2,102),5) else '' end ) 'Q1',
  max(case dept when 'Q2' then right(convert(varchar(10),time1,102),5) + '-' + right(convert(varchar(10),time2,102),5) else '' end ) 'Q2'
from tb
group by uname
/*
uname      Q1                    Q2                    
---------- --------------------- --------------------- 
a          10.01-10.02           10.03-10.04
b          10.08-10.09           10.10-10.11
c          10.15-10.16           10.17-10.18
d          10.22-10.23           10.24-10.25

（所影响的行数为 4 行）
*/

--动态SQL,指dept不止Q1,Q2两种．
declare @sql varchar(8000)
set @sql = 'select uname'
select @sql = @sql + ' , max(case dept when ''' + dept + ''' then right(convert(varchar(10),time1,102),5) + ''-'' + right(convert(varchar(10),time2,102),5) else '' '' end) [' + dept + ']'
from (select distinct dept from tb) as a
set @sql = @sql + ' from tb group by uname'
exec(@sql) 
/*
uname      Q1                    Q2                    
---------- --------------------- --------------------- 
a          10.01-10.02           10.03-10.04
b          10.08-10.09           10.10-10.11
c          10.15-10.16           10.17-10.18
d          10.22-10.23           10.24-10.25
*/

drop table tb

[/code]

[pt1314917]

create table tb(id int,uname varchar(10),dept varchar(10),time1 datetime,time2 datetime)
insert into tb values(1,         'a',         'Q1',         '2007-10-01',         '2007-10-02')   
insert into tb values(2,         'b',         'Q1',         '2007-10-08',         '2007-10-09')   
insert into tb values(3,         'c',         'Q1',         '2007-10-15',         '2007-10-16')   
insert into tb values(4,         'd',         'Q1',         '2007-10-22',         '2007-10-23')   
insert into tb values(5,         'a',         'Q2',         '2007-10-03',         '2007-10-04')   
insert into tb values(6,         'b',         'Q2',         '2007-10-10',         '2007-10-11')   
insert into tb values(7,         'c',         'Q2',         '2007-10-17',         '2007-10-18')   
insert into tb values(8,         'd',         'Q2',         '2007-10-24',         '2007-10-25')   
go


declare @sql varchar(8000)
set @sql='select uname'
select @sql=@sql+',['+dept+']=max(case dept when '''+ dept + ''' then right(convert(varchar(10),time1,102),5)+''-''+right(convert(varchar(10),time2,102),5)
else '''' end)' from (select distinct dept from tb)a
set @sql=@sql+' from tb group by uname'
exec(@sql)

[中国风]

2005,没测过

select uname,[Q1],[Q2]
from 
(select uname,dept,
right(convert(varchar(10),time1,102),5)+'-'+right(convert(varchar(10),time2,102),5) as row
from 
tb)T
pivot
(Max(row) for dept in([Q1],[Q2])
)tmp2

[hyqwan]

借宝地问一下如果是上面的动态SQL 想把结果用做Reporting service的数据源,但是列是动态的,请问怎么实现呢?

[中国风]

回楼上兄弟：以下05动态的列子..
http://blog.csdn.net/roy_88/archive/2007/02/13/1509413.aspx