SQL根据键和值类型表(PIVOT、CASE等)返回值作为字段

Question

使用SQL Server 2005，我有以下表:

的值:

ValueID  UserID    ValueTypeID  ValueKey  Text  Number
------------------------------------------------------
1        345       1            NAME      Bob   NULL
2        345       2            AGE       NULL  30
3        689       1            NAME      Sam   NULL
4        689       2            AGE       NULL  45

ValueType:

ValueTypeID  CodeName
---------------------
1            TEXT
2            NUMBER

我希望得到这样的结果:

UserID  Name  Age
-----------------
345     Bob   30
689     Sam   45

我确信这是可能的，使用一个枢轴和一些案例语句?有更多的valuekey，甚至更多的valuetype，但这将使我继续。我事先知道ValueKeys的列表，所以这应该意味着我可以避免动态查询。

Answer 1

经典的数据透视查询。然后，如果需要，可以使用ValueTypeID:

SELECT UserID, 
       MAX(
           CASE WHEN ValueKey = 'NAME' 
                THEN 
                    CASE WHEN ValueTypeID = 1 
                             THEN Text 
                         WHEN ValueTypeID = 2
                             THEN Number 
                         WHEN ValueTypeID = 3
                             THEN Date
                         ELSE NULL
                    END
                ELSE NULL 
           END
       ) AS Name,
       MAX(
           CASE WHEN ValueKey = 'AGE' 
                THEN 
                    CASE WHEN ValueTypeID = 1 
                             THEN Text 
                         WHEN ValueTypeID = 2
                             THEN Number 
                         WHEN ValueTypeID = 3
                             THEN Date
                         ELSE NULL
                    END
                ELSE NULL 
           END
       ) AS Age,
       MAX(
           CASE WHEN ValueKey = 'DATEBIRTH' 
                THEN 
                    CASE WHEN ValueTypeID = 1 
                             THEN Text 
                         WHEN ValueTypeID = 2
                             THEN Number 
                         WHEN ValueTypeID = 3
                             THEN Date
                         ELSE NULL
                    END
                ELSE NULL 
           END
       ) AS DateBirth
FROM Value
GROUP BY UserID

但它可能是无用的，你可以用更少的代码少一些泛型:

SELECT UserID, 
       MAX(
           CASE WHEN ValueKey = 'NAME' 
                THEN Text -- I know that name is a string
                ELSE NULL 
           END
       ) AS Name,
       MAX(
           CASE WHEN ValueKey = 'AGE' 
                THEN Number -- I know that age is a number
                ELSE NULL 
           END
       ) AS Age,
       MAX(
           CASE WHEN ValueKey = 'DATEBIRTH' 
                THEN Date -- I know that date of birth is a date
                ELSE NULL 
           END
       ) AS DateBirth
FROM Value
GROUP BY UserID

Answer 2

解决方案应该是重新设计表。valueType查找在实现时是完全无用的。你应该只有一个单一的"ValueText"列，而不是"文本"，"数字"，…把所有的值存储在1列中。那么它就是一个简单的join表述。

Answer 3

您可以按UserID分组，并使用子查询来获取字段值。

declare @T table
(
  ValueID int,
  UserID int,
  ValueTypeID int,
  ValueKey varchar(10),
  Text varchar(10),
  Number int
)
insert into @T values
(1,        345,       1,            'NAME',      'Bob',   NULL),
(2,        345,       2,            'AGE',       NULL,    30),
(3,        689,       1,            'NAME',      'Sam',   NULL),
(4,        689,       2,            'AGE',       NULL,    45)
select T1.UserID,
       (select [Text]
        from @T as T2
        where T1.UserID = T2.UserID and
              T2.ValueKey = 'NAME') as Name,
       (select Number
        from @T as T2
        where T1.UserID = T2.UserID and
              T2.ValueKey = 'AGE') as Age
from @T as T1
group by T1.UserID