sever 2008中如何在一列中splitProjects
sever 2008中如何在一列中splitProjects
提问于 2013-04-23 10:59:23
WITH MYTABLE AS (
select Executive, count([Cat A]) AS [1-3], count([Cat B]) AS [4-6],count([Cat C]) AS [7-10],count([Cat D]) AS [11-15],count([Cat E]) AS [16+]
from (
select
Executive,
case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 0 and DATEDIFF(d,[next follow up date],getdate()) < 4)
then 'A'
end as [Cat A],
case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 3 and DATEDIFF(d,[next follow up date],getdate()) < 7)
then 'B'
end as [Cat B],
case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 6 and DATEDIFF(d,[next follow up date],getdate()) < 11)
then 'C'
end as [Cat C],
case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 10 and DATEDIFF(d,[next follow up date],getdate()) < 16)
then 'D'
end as [Cat D],
case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 15)
then 'E'
end as [Cat E]
from vw_FollowUps)
as Table1
group by Executive, [Cat A], [Cat B], [Cat C], [Cat D], [Cat E]
)
Select T1.Executive, SUM([1-3]) AS [1-3],SUM([4-6]) AS [4-6],SUM([7-10]) AS [7-10],SUM([11-15]) AS [11-15],SUM([16+]) AS [16+]
from MYTABLE T1
GROUP BY T1.Executivei got output below
Executive 1-3 4-6 7-10 11-15 16+
kushali 1 2 0 1 2
nirupama 0 1 1 3 1但是我需要-here下面的输出，我想要项目列，以及我们如何将项目分割成一列
Executive Projects 1-3 4-6 7-10 11-15 16+
kushali swgruha,runi,nanuapa 1 2 0 1 2
nirupama swgruha,runi,nanuapa 1 2 0 1 2回答 1
Stack Overflow用户
发布于 2013-04-23 18:15:59
从您的示例来看，您似乎是在询问如何以这种形式总结一系列记录.
kushali swgruha 1
kushali runi 1
kushali nanuapa 2..。以下表格：
kushali swgruha,runi,nanuapa 4..。以便在第一列上分组，将第二列聚合为CSV (逗号分隔变量)字符串，并在第三列上执行和。
这是可以做到的，而且有多种方法。
在不知道哪个表包含要聚合为CSV字符串的字符串的情况下，我无法给出确切的SQL语句。但是你可以找到一个逐步的例子这里。(免责声明:那是我的博客。)您可以使用CTE和一些XML函数来完成这个任务。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16167609
复制相关文章
相似问题
腾讯云开发者
