数据矩阵转换，不是简单的行列转换， 求指点

[shoppo0505]

现在有如下测试数据：;
WITH data(InsertDate, FieldName, FieldValue) AS
(
select '2017-9-01', 'FieldName1', 9.1 UNION ALL
select '2017-9-01', 'FieldName2', 9.2 UNION ALL
select '2017-9-01', 'FieldName3', 9.3 UNION ALL
select '2017-9-01', 'FieldName4', 9.4 UNION ALL
select '2017-9-01', 'FieldName5', 9.5 UNION ALL
select '2017-10-01', 'FieldName1', 10.1 UNION ALL
select '2017-10-01', 'FieldName2', 10.2 UNION ALL
select '2017-10-01', 'FieldName3', 10.3 UNION ALL
select '2017-10-01', 'FieldName4', 10.4 UNION ALL
select '2017-10-01', 'FieldName5', 10.5 UNION ALL
select '2017-11-01', 'FieldName1', 11.1 UNION ALL
select '2017-11-01', 'FieldName2', 11.2 UNION ALL
select '2017-11-01', 'FieldName3', 11.3 UNION ALL
select '2017-11-01', 'FieldName4', 11.4 UNION ALL
select '2017-11-01', 'FieldName5', 11.5
)
select * from data

我想得到如下格式：
FieldName 2017-9-01 2017-10-01 2017-11-01
FieldName1 9.1 10.1 11.1
FieldName2 9.2 10.2 11.2
FieldName3 9.3 10.3 11.3
FieldName4 9.4 10.4 11.4
FieldName5 9.5 10.5 11.5

似乎不是简单的行列转换，请问有什么快速有效的转换方法么？

[二月十六]

这样？

--测试数据
if not object_id('tab') is null  
    drop table tab  
Go  
Create table tab(InsertDate DATE, FieldName NVARCHAR(100), FieldValue NVARCHAR(100))  
Insert tab  
select '2017-9-01', 'FieldName1', 9.1 UNION ALL
select '2017-9-01', 'FieldName2', 9.2 UNION ALL
select '2017-9-01', 'FieldName3', 9.3 UNION ALL
select '2017-9-01', 'FieldName4', 9.4 UNION ALL
select '2017-9-01', 'FieldName5', 9.5 UNION ALL
select '2017-10-01', 'FieldName1', 10.1 UNION ALL
select '2017-10-01', 'FieldName2', 10.2 UNION ALL
select '2017-10-01', 'FieldName3', 10.3 UNION ALL
select '2017-10-01', 'FieldName4', 10.4 UNION ALL
select '2017-10-01', 'FieldName5', 10.5 UNION ALL
select '2017-11-01', 'FieldName1', 11.1 UNION ALL
select '2017-11-01', 'FieldName2', 11.2 UNION ALL
select '2017-11-01', 'FieldName3', 11.3 UNION ALL
select '2017-11-01', 'FieldName4', 11.4 UNION ALL
select '2017-11-01', 'FieldName5', 11.5

DECLARE @sql VARCHAR(8000)
SET @sql = 'select FieldName'
SELECT  @sql = @sql + ' , max(case InsertDate when ''' + RTRIM(InsertDate)
        + ''' then FieldValue else null end) [' + RTRIM(InsertDate) + ']'
FROM    ( SELECT DISTINCT
                    InsertDate
          FROM      tab
        ) AS a
ORDER BY InsertDate
SET @sql = @sql + ' from tab group by FieldName'
EXEC(@sql) 

[二月十六]

--测试数据
if not object_id('tab') is null  
    drop table tab  
Go  
Create table tab(InsertDate DATE, FieldName NVARCHAR(100), FieldValue NVARCHAR(100))  
Insert tab  
select '2017-9-01', 'FieldName1', 9.1 UNION ALL
select '2017-9-01', 'FieldName2', 9.2 UNION ALL
select '2017-9-01', 'FieldName3', 9.3 UNION ALL
select '2017-9-01', 'FieldName4', 9.4 UNION ALL
select '2017-9-01', 'FieldName5', 9.5 UNION ALL
select '2017-10-01', 'FieldName1', 10.1 UNION ALL
select '2017-10-01', 'FieldName2', 10.2 UNION ALL
select '2017-10-01', 'FieldName3', 10.3 UNION ALL
select '2017-10-01', 'FieldName4', 10.4 UNION ALL
select '2017-10-01', 'FieldName5', 10.5 UNION ALL
select '2017-11-01', 'FieldName1', 11.1 UNION ALL
select '2017-11-01', 'FieldName2', 11.2 UNION ALL
select '2017-11-01', 'FieldName3', 11.3 UNION ALL
select '2017-11-01', 'FieldName4', 11.4 UNION ALL
select '2017-11-01', 'FieldName5', 11.5

DECLARE @name VARCHAR(MAX) ,
    @sql VARCHAR(MAX)
SET @name = STUFF(( SELECT DISTINCT
                            ',[' + RTRIM(InsertDate) + ']'
                    FROM    tab
                  FOR
                    XML PATH('')
                  ), 1, 1, '')
SET @sql = ''
SET @sql = @sql
    + 'SELECT * from tab pivot(max([FieldValue])for InsertDate in(' + @name
    + '))a'
EXEC( @sql)

[shoppo0505]

如果可能不知道还有没有其他办法，个人感觉字符串拼接是下下策。

[二月十六]

引用 5 楼 shoppo0505 的回复:

如果可能不知道还有没有其他办法，个人感觉字符串拼接是下下策。

如果时间不是固定的，只能用动态拼接，别无他法。

[碧水幽幽泉]

LZ，你这种情况，需要自定义一个用动态拼接的存储过程或视图。

[shoppo0505]

这还没人接？钱还不够多么？
我抛砖引玉一下，如下是我的做法：
;WITH data(InsertDate, FieldName, FieldValue) AS
(
select '2017-9-01', 'FieldName1', 9.1 UNION ALL
select '2017-9-01', 'FieldName2', 9.2 UNION ALL
select '2017-9-01', 'FieldName3', 9.3 UNION ALL
select '2017-9-01', 'FieldName4', 9.4 UNION ALL
select '2017-9-01', 'FieldName5', 9.5 UNION ALL
select '2017-10-01', 'FieldName1', 10.1 UNION ALL
select '2017-10-01', 'FieldName2', 10.2 UNION ALL
select '2017-10-01', 'FieldName3', 10.3 UNION ALL
select '2017-10-01', 'FieldName4', 10.4 UNION ALL
select '2017-10-01', 'FieldName5', 10.5 UNION ALL
select '2017-11-01', 'FieldName1', 11.1 UNION ALL
select '2017-11-01', 'FieldName2', 11.2 UNION ALL
select '2017-11-01', 'FieldName3', 11.3 UNION ALL
select '2017-11-01', 'FieldName4', 11.4 UNION ALL
select '2017-11-01', 'FieldName5', 11.5
)
--select * from data
, tempData AS
(
select
FieldName
,case when InsertDate = '2017-9-01' THEN FieldValue ELSE NULL end AS '2017-9-01'
,case when InsertDate = '2017-10-01' THEN FieldValue ELSE NULL end AS '2017-10-01'
,case when InsertDate = '2017-11-01' THEN FieldValue ELSE NULL end AS '2017-11-01'
from data
)
select FieldName
, SUM(ISNULL(CONVERT(decimal(18,2),tempData.[2017-9-01]),0)) AS '1'
, SUM(ISNULL(CONVERT(decimal(18,2),tempData.[2017-10-01]),0)) AS '2'
, SUM(ISNULL(CONVERT(decimal(18,2),tempData.[2017-11-01]),0)) AS '3'
from tempData
group by FieldName

但是列名不能满足我的要求，不是动态使用日期，谁能帮忙修改下

[卖水果的net]

如果不固定，就要动态拼接sql l。

[LongRui888]

这个列名没办法动态，只能是手写上去的