将行转换为SQL Server 2008列

Table name is UserData and name column is PRIMARY KEY ,I have a query returning the following like

表名是UserData,name列是PRIMARY KEY,我有一个返回如下的查询

select name , jobProfile from UserData

从UserData中选择名称,jobProfile

name  |  jobProfile

---

  a   |   Admin
  b   |   user
  c   |   employee
  d   |   Admin
  e   |   user
  f   |   user
  g   |   employee

I would like to make the results like so (if possible)

我想这样的结果(如果可能的话)

 user  | employee |  Admin
___________________________
 b     |  c       |  a
 e     |  g       |  d
 f     |  null    |  null

2 个解决方案

#1

Not sure I understand why people trying to close this question. Seems like legit question to me and not exactly a duplicate of referenced question. Referenced question has 3 columns as input, while this question has two. So there is an extra trick needed.

不确定我理解为什么人们试图关闭这个问题。对我来说似乎是合法的问题而不是引用问题的重复。引用的问题有3列作为输入,而这个问题有两列。所以需要一个额外的技巧。

-- test data
declare @UserData table (Name varchar(10), JobProfile varchar(10))

insert @UserData
values
  ('a' ,  'Admin'),
  ('b' ,  'user'),
  ('c' ,  'employee'),
  ('d' ,  'Admin'),
  ('e' ,  'user'),
  ('f' ,  'user'),
  ('g' ,  'employee')

-- query
select [user], [employee], [Admin]
from
(
    select *, row_number() over (partition by JobProfile order by Name) RowNumber
    from @UserData
) as p
pivot
(
    min(Name) for JobProfile in ([user], [employee], [Admin])
) as t
order by RowNumber

The output:

user       employee   Admin
---------- ---------- ----------
b          c          a
e          g          d
f          NULL       NULL

#2

Since you didn't provide very much information, or any rules that govern the transformation, the only answer I can give is check out the PIVOT function.

由于您没有提供太多信息或任何管理转换的规则,我能给出的唯一答案是检查PIVOT功能。