以列名的形式获取行

Question

SQL Server 2008中的输入表格式：

            *Name*                                 *Department*

1)          abcd                                    IT
2)          abcd                                    CTECH
3)          abcd                                    MECH
4)          uvw                                     SAP
5)          uvw                                     Informatica

期望的输出结果：

         Name     Department1      Department2
1)       abcd      IT              CTECH
2)       uvw       SAP             Informatica

以上是我的SQL Server 2008表的方案...

谁能帮我得到如下所示的输出结果格式？

Answer 1

有几种方法可以将数据转换为列。

您可以将聚合函数与CASE表达式一起使用：

select name,
  max(case when rn = 1 then department end) Department1,
  max(case when rn = 2 then department end) Department2,
  max(case when rn = 3 then department end) Department3
from
(
  select name,
    department,
    row_number() over(partition by name order by department) rn
  from yt
) src
group by name;

请参阅SQL Fiddle with Demo

您可以使用PIVOT将数据从行转换为列。

select *
from
(
  select name,
    department,
    'Department'
        +cast(row_number() over(partition by name order by department) as varchar(10)) col
  from yt
) src
pivot
(
  max(department)
  for col in ([Department1], [Department2], [Department3])
) piv;

请参阅SQL Fiddle with Demo

我建议使用动态SQL来获取结果，因为每个名称可能包含未知数量的部门。动态SQL代码为：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(col) 
                    from
                    (
                      select 'department'+
                        cast(row_number() over(partition by name order by department) as varchar(10)) col
                      from yt
                    ) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT name, ' + @cols + ' 
            from 
             (
                select name, department,
                  ''department''+
                        cast(row_number() over(partition by name order by department) as varchar(10)) col
                from yt
            ) x
            pivot 
            (
                max(department)
                for col in (' + @cols + ')
            ) p '

execute(@query);

请参阅SQL Fiddle with Demo

Answer 2

declare @maxDept int
select @maxDept = max(numDepts)
from
(
    select  name, numDepts = count(dept) from depts group by name
) a

declare @deptPivot varchar(max)
set @deptPivot = ''
declare @counter int
set @counter = 1
while @counter <= @maxdept 
begin
   if (@deptPivot) = '' 
      set @deptPivot = 'department' + convert(varchar, @counter)
   else
      set @deptPivot = @deptPivot + ', department' + convert(varchar, @counter)

   set @counter = @counter + 1
end

declare @sql varchar(max)
set @sql = '
select
name, ' + @deptPivot + '
from
(
    select 
    name,
    id = ''department'' + convert(varchar, row_number() over (partition by name order by dept asc)),
    dept
    from depts
) a
pivot
(
   max(dept) for id in (' + @deptPivot + ')
) as pvt'

--print @sql
exec (@sql)

Answer 3

假设：OP希望只考虑top 2 Departments作为名称。

1.为Ranking创建临时表

select 
ROW_NUMBER()over(partition by Name order by Name) AS ver
,Name
,Department
into #temp1
from table_name;

2.现在我们使用下面的Query

select 
DISTINCT 
t1.Name,
t2.Department as Department1,
t3.Department as Department2
from #temp1 t1
left join #temp1 t2 on t1.Name=t2.Name and t2.ver=1
left join #temp1 t3 on t1.Name=t3.Name and t3.ver=2