求一SQL语句

[wsxcdx]

数据如下：
create table WkResult
(Week varchar(12),
StartDate varchar(10),
Qty int,
Total numeric(11,4)
)
go
insert into WkResult
select 'wk10','2015-03-02',1000,1200 union all
select 'wk11','2015-03-09',800,900 union all
select 'wk12','2015-03-16',1100,1150 union all
select 'wk13','2015-03-23',900,1020 union all
select 'wk14','2015-03-30',1300,1380 union all
select 'wk15','2015-04-06',1500,2300 union all
select 'wk16','2015-04-13',2000,3960
------------
--需要的结果
------------
Week wk10 wk11 wk12 wk13 wk14 wk15 wk16
StartDate 2015-03-02 2015-03-09 2015-03-16 2015-03-23 2015-03-30 2015-04-06 2015-04-13
Qty 1000 800 1100 900 1300 1500 2000
Total 1200 900 1150 1020 1380 2300 3960
-------------------
数据条数不确定，求一动态SQL语句，谢谢!

[Tiger_Zhao]

DECLARE @sql varchar(max)
DECLARE @columns varchar(max)

SET @columns = ''

SELECT @columns = @columns+',['+week+']'
  FROM WkResult

SET @columns = STUFF(@columns,1,1,'')

SET @sql = '
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),startDate,120) value
          FROM WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ('+@columns+')
       ) p
UNION ALL
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),qty) value
          FROM WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ('+@columns+')
       ) p
UNION ALL
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),Total) value
          FROM WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ('+@columns+')
       ) p'

PRINT @sql

EXEC(@sql)

SELECT *
  FROM (
        SELECT week, Convert(varchar(10),startDate,120) value
          FROM #WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ([wk10],[wk11],[wk12],[wk13],[wk14],[wk15],[wk16])
       ) p
UNION ALL
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),qty) value
          FROM #WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ([wk10],[wk11],[wk12],[wk13],[wk14],[wk15],[wk16])
       ) p
UNION ALL
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),Total) value
          FROM #WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ([wk10],[wk11],[wk12],[wk13],[wk14],[wk15],[wk16])
       ) p

wk10       wk11       wk12       wk13       wk14       wk15       wk16
---------- ---------- ---------- ---------- ---------- ---------- ----------
2015-03-02 2015-03-09 2015-03-16 2015-03-23 2015-03-30 2015-04-06 2015-04-13
1000       800        1100       900        1300       1500       2000
1200.0000  900.0000   1150.0000  1020.0000  1380.0000  2300.0000  3960.0000

[wsxcdx]

引用 1 楼 Tiger_Zhao 的回复:

DECLARE @sql varchar(max)
DECLARE @columns varchar(max)

SET @columns = ''

SELECT @columns = @columns+',['+week+']'
  FROM WkResult

SET @columns = STUFF(@columns,1,1,'')

SET @sql = '
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),startDate,120) value
          FROM WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ('+@columns+')
       ) p
UNION ALL
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),qty) value
          FROM WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ('+@columns+')
       ) p
UNION ALL
SELECT *
  FROM (
        SELECT week, Convert(varchar(10),Total) value
          FROM WkResult
       ) t
 PIVOT (
        MAX(value)
        FOR week IN ('+@columns+')
       ) p'

PRINT @sql

EXEC(@sql)

谢谢 Tiger_Zhao, 想把这个动态语句的结果插入到临时表，列数不确定，有什么简单的办法吗？

[Tiger_Zhao]

做不到的。
动态语句创建出来的临时表上层调用者不可见。

不过你这个格式比较固定，有不是很好的变通方法：
上层可以建一个大的临时表，包括了所有的列#result(week,wk1,wk2,...,wk54)。
动态语句采用指定列的方式插入
[code=sql]INSERT INTO #result(week,[wk10],[wk11],[wk12],[wk13],[wk14],[wk15],[wk16])
SELECT ...
UNION ALL
SELECT ...
UNION ALL
SELECT ...[code]

[wsxcdx]

谢谢！结帖了。