在sql server 2005中转换colums中的行

I’ve been looking for a way to show one rows in multiple colums, one cell. The content of it separated by comma’s.

我一直在寻找一种方法来显示多个列中的一行,一个单元格。它的内容用逗号分隔。

For example, in stead of:

例如,代替:

ProjectID      Label
————           ——–
1200           label1
1200           label2
1200           label3

I would like the result of my query to look like this:

我希望我的查询结果如下所示:

ProjectID                      1        2        3
————                   ——– 
1200                          label1  label2, label3

thanks in advance

提前致谢

1 个解决方案

#1

This is a little more challenging than just the use of pivot, but only a little. The problem is that you don't have a column to specify where the labels go, so you have to add it.

这比使用枢轴更具挑战性,但只是一点点。问题是您没有列来指定标签的位置,因此您必须添加它。

Here is a solution using aggregation:

以下是使用聚合的解决方案:

with t as (
      select t.*, row_number() over (partition by ProjectId order by label) as seqnum
      from t
)
select ProjectId,
       max(case when seqnum = 1 then label end) as [1],
       max(case when seqnum = 2 then label end) as [2],
       max(case when seqnum = 3 then label end) as [3]
from t
group by ProjectId;

Here is the solution using pivot:

以下是使用pivot的解决方案:

with t as (
      select t.*, row_number() over (partition by ProjectId order by label) as seqnum
      from t
)
select ProjectId, [1], [2], [3]
from t
pivot (max(label) for seqnum in ([1], [2], [3])
      ) as pvt

#1