我需要转换,转置,无论正确的术语是包含多行的数据集,而不是返回包含多列的单行。以下是我开始时使用的数据示例
EID PM Project HOURS WeekStarting
joe@test.com tom@test.com Proj A 6 11/28/2016
joe@test.com tom@test.com Proj A 3 12/5/2016
joe@test.com tom@test.com Proj A 7 12/12/2016
joe@test.com tom@test.com Proj A 3 12/19/2016
sue@test.com sam@test.com Proj B 3 11/28/2016
sue@test.com sam@test.com Proj B 6 12/12/2016
sue@test.com sam@test.com Proj B 7 12/19/2016我想这样格式化数据
EID PM Project Week1 Week2 Week3 Week4
joe@test.com tom@test.com Proj A 6 3 7 3
sue@test.com sam@test.com Proj B 3 0 6 7请注意,对于sue,我的源数据中没有week2中的数据,因此第二周的结果为0。
目前,我正在使用Javascript中的reduce函数来做这件事,但它非常混乱。我宁愿尝试修复数据并拥有干净的Javascript。
任何帮助都是非常感谢的。
发布于 2016-11-23 04:32:55
Declare @SQL varchar(max)
Select @SQL = Stuff((Select ','+QuoteName('Week'+cast(WeekNr as varchar(25)))
From (Select Distinct Top 100 Percent WeekNr=Dense_Rank() over (Order By WeekStarting) From YourTable
Order By 1) A
For XML Path ('')),1,1,'')
Select @SQL = 'Select EID,PM,Project,' + @SQL + '
From (
Select EID
,PM
,Project
,Item = ''Week''+cast(Dense_Rank() over (Order By WeekStarting) as varchar(25))
,Val = Hours
From YourTable
) A
Pivot (max(Val) For Item in (' + @SQL + ') ) p'
Exec(@SQL);返回

发布于 2016-11-23 04:37:49
您可以像这样查询:
;with cte as (
select *, DATEPART(WEEK, weekstarting) -
DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,weekstarting), 0)) + 1 AS WEEK_OF_MONTH
from yourtranspose
)
select Eid, PM, Project, [1], [2], [3], [4], [5] from
( select EID, PM, Project, WEEK_OF_MONTH , hours from cte ) a
pivot (max(hours) for week_of_month in ([1], [2], [3], [4], [5])) p发布于 2016-11-23 03:49:30
对于每个额外的行,您需要针对表本身来连接表
https://stackoverflow.com/questions/40750384
复制相似问题