在表格中旋转单个字段

I have a table of the following structure:

我有一个以下结构的表:

Name Type Line UniqueID Key Value

名称类型行UniqueID键值

I need to select all the fields except UniqueID field and add a key 'UniqueID' and a Value field with the value of the UniqueID to the result set.

我需要选择除UniqueID字段以外的所有字段,并向结果集添加一个键“UniqueID”和一个带有UniqueID值的Value字段。

For example if I have three records:

例如,如果我有三个记录:

Name            Type      Line   UniqueID      Key       Value  
___________________________________________________________________
John Doe        Employee   2     test333       SSN       123-45-2345
John Doe        Employee   3     test333       Address   555 Rodeo Drive
Jane Doe        Visitor    2     test444       SSN       345-67-8907

my result set would need to be:

我的结果集需要是:

Name         Type       Line  Key        Value  
___________________________________________________
John Doe     Employee    2    UniqueID   test333
John Doe     Employee    2    SSN        123-45-2345
John Doe     Emplyee     3    Address    555 Rodeo Drive
Jane Doe     Visitor     2    UniqueID   test444
Jane Doe     Visitor     2    SSN        345-67-8907

Do I use pivots? Can anyone point me into the right direction?

我使用枢轴吗?有人能指出我正确的方向吗?

1 个解决方案

#1

This is kind of an odd request, at least to me. But anyhow, you can approach it a few ways. One way is to use cross apply:

这是一个奇怪的要求,至少对我而言。但无论如何,你可以通过几种方式来解决它。一种方法是使用交叉申请:

select
name,
type,
line,
newkey,
newvalue
from 
table1
cross apply
( values
  ('UniqueID',uniqueid),
  ('SSN',Value),
  ('Address',Value)
) c
(newkey,newvalue)

SQL Fiddle demo

SQL小提琴演示

Based on your comments, we'll chuck the union all method.

根据你的评论,我们将把联合所有的方法扔掉。

Here is an incredibly ugly and kludgy unpivot, combined with a union (to get the unique ids):

这是一个令人难以置信的丑陋和kludgy的univot,结合了一个联盟(以获得独特的ID):

select
name,
type,
line,
newkey2,
newvalue
from
(select
name,
type,
line,
[key],
[value]
from table1) t1
unpivot
(
  newvalue for newkey in ([Value])  
 ) u
 unpivot
 (newkey2 for newvalue2 in ([key])) u2
 union all
 select
 name,
 type,
 line,
 'UniqueID',
 Uniqueid
 from
 table1

Unpivot SQL Fiddle

#1