在键值对表中旋转数据

I have a standard table like this:

我有一个像这样的标准表:

MyKey   MyValue
--------------------------------
Var1    20
Var2    30
Var3    20
Var4    15
Var5    6

And I want it to look like this:

我希望它看起来像这样:

Var1        Var2        Var3        Var4
--------------------------------------------
20          30          20          15

The data is not grouped, or calculated. I've found and tried a number of things so far:

数据未分组或计算。到目前为止,我已经找到并尝试了很多东西:

SELECT 
    Var1,
    Var2,
    Var2,
    Var4
FROM 
    (select * 
     from MyTable
    ) T
PIVOT
(
    max(MyValue)
    FOR MyKey in 
    (
        'Var1', 'Var2', 'Var3', 'Var4'
    )
)
as V

This works, but I get multiple rows.

这有效,但我得到多行。

Also, I tried using CASE ... WHEN:

此外,我尝试使用CASE ......时间:

Select MyValue,
 Min(Case MyKey When 'Var1' Then MyKey End) Var1,
 Min(Case MyKey When 'Var2' Then MyKey End) Var2,
 ...
 FROM MyTable
 WHERE MyKey in 
 ('Var1', 'Var2', ...)

This doesn't work because I have nothing to group on.

这不起作用,因为我没有任何分组。

Is what I'm trying to do possible, and if so, how?

我正在尝试做什么,如果是这样,怎么样?

EDIT:

Okay, I think I've determined what's causing the difference, I've edited the SQL Fiddle. Basically, the table has an IDENTITY column. I didn't realise this would make a different, but it makes a marked on.

好吧,我想我已经确定了造成差异的因素,我编辑了SQL Fiddle。基本上,该表具有IDENTITY列。我没有意识到这会有所不同,但它标志着。

1 个解决方案

#1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

MS SQL Server 2008架构设置:

CREATE TABLE Table1
    ([pk] [int] IDENTITY(1,1) NOT NULL,
     [MyKey] nvarchar(4) not null, 
     [MyValue] nvarchar(30) not null)
;

INSERT INTO Table1
    ([MyKey], [MyValue])
VALUES
    ('Var1', '20'), ('Var2', '30'),
    ('Var3', '20'), ('Var4', '15'),
    ('Var5', 'aaa')
;

Query 1:

SELECT 
    Var1,
    Var2,
    Var3,
    Var4
FROM 
    (select [MyKey], [MyValue]
     from Table1
    ) AS T
PIVOT
(
    max(MyValue)
    FOR MyKey in 
    (
        Var1, Var2, Var3, Var4
    )
)
 AS V

Results:

| VAR1 | VAR2 | VAR3 | VAR4 |
|------|------|------|------|
|   20 |   30 |   20 |   15 |