如何拆分项目在一个列在sql server 2008

Question

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.Executive

---

i 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

---

但是我需要下面的输出-这里我想要项目列，以及我们如何在一列中拆分项目

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

Answer 1

从你的例子来看，你似乎是在问如何以这种形式总结一系列记录…

 kushali      swgruha    1
 kushali      runi       1
 kushali      nanuapa    2

…到这个表单:

kushali      swgruha,runi,nanuapa      4

…以便对第一列进行GROUP，将第二列聚合为CSV(逗号分隔的变量)字符串，并对第三列执行SUM。

这是可以做到的，并且有多种方法。

不知道哪个表包含你想聚合成CSV字符串的字符串，我不能给你一个确切的SQL语句。但是您可以在这里找到一个循序渐进的示例。(免责声明:这是我的博客。)您可以使用cte和一些XML函数来实现。