行列转换
CREATE TABLE test
(
id INT ,
NAME VARCHAR(20) ,
dt DATETIME ,
f1 VARCHAR(20) ,
f2 VARCHAR(20)
)
INSERT INTO test VALUES(1,'早上','20121115','at','dt')
INSERT INTO test VALUES(2,'中午','20121115','bz','tf')
INSERT INTO test VALUES(3,'早上','20121116','tf','8d')
INSERT INTO test VALUES(4,'中午','20121116','ef','tf')
INSERT INTO test VALUES(5,'早上','20121117','ad','dt')
INSERT INTO test VALUES(6,'中午','20121117','abc','3d')
INSERT INTO test VALUES(7,'早上','20121118','efg','dt')
INSERT INTO test VALUES(8,'中午','20121119','ut','rd')

SELECT * FROM test
idNAMEdtf1f2
1早上2012-11-15 atdt
2中午2012-11-15 bztf
3早上2012-11-16tf8d
4中午2012-11-16 eftf
5早上2012-11-17 addt
6中午2012-11-17 abc3d
7早上2012-11-18efgdt
8中午2012-11-19 utrd
..................
即按name分组，按dt字段分将多行合并为多列(将dt字段按周合并即周一到周日)，如何操作
如下图所示


[解决办法]

SELECT 
NAME
,MAX(CASE WHEN DATEPART(weekday,dt)=1 THEN f1 ELSE '' END) AS 周一f1
,MAX(CASE WHEN DATEPART(weekday,dt)=1 THEN f2 ELSE '' END) AS 周一f1
,MAX(CASE WHEN DATEPART(weekday,dt)=2 THEN f1 ELSE '' END) AS 周二f1
,MAX(CASE WHEN DATEPART(weekday,dt)=2 THEN f2 ELSE '' END) AS 周二f2
,MAX(CASE WHEN DATEPART(weekday,dt)=3 THEN f1 ELSE '' END) AS 周三f1
,MAX(CASE WHEN DATEPART(weekday,dt)=3 THEN f2 ELSE '' END) AS 周三f2
,MAX(CASE WHEN DATEPART(weekday,dt)=4 THEN f1 ELSE '' END) AS 周四f1
,MAX(CASE WHEN DATEPART(weekday,dt)=4 THEN f2 ELSE '' END) AS 周四f2
,MAX(CASE WHEN DATEPART(weekday,dt)=5 THEN f1 ELSE '' END) AS 周五f1
,MAX(CASE WHEN DATEPART(weekday,dt)=5 THEN f2 ELSE '' END) AS 周五f2
,MAX(CASE WHEN DATEPART(weekday,dt)=6 THEN f1 ELSE '' END) AS 周六f1
,MAX(CASE WHEN DATEPART(weekday,dt)=6 THEN f2 ELSE '' END) AS 周六f2
,MAX(CASE WHEN DATEPART(weekday,dt)=7 THEN f1 ELSE '' END) AS 周日f1
,MAX(CASE WHEN DATEPART(weekday,dt)=7 THEN f2 ELSE '' END) AS 周日f2
FROM test
GROUP BY NAME
ORDER BY MIN(id) ASC