透视域的元数据

Question

我有一个基于复杂数据库设置的复杂问题。

我有一张表，上面有fieldId，objectId，fieldValue。每个对象在该表中都有许多行-每个字段占一行。

示例-有名字、道布、体重、身高、学校等字段

object (id:1)可以包含以下行：

1.1.'john'
1.2.'march-31-2000'
1.3.155
1.4.60
1.5.'ps 176'

我有一个网格，它将为每个字段显示一列，为每个对象显示一行-列名为firstName、道布、体重、身高、学校，并且我的数据库中的所有对象都将有行

我用了一个轴心来获取这些数据-

就像这样-

SELECT *
  FROM #temp123
 PIVOT (
          MAX(fieldValue)
          FOR [Variable] IN ([firstName],[dob],[weight],[height],[school])
       ) AS p

这可以很好地显示数据。然而，现在是时候在我的网格中添加排序了。我希望int列的排序方式类似于int (101>99)，而date列的显示方式类似于日期(1/1/2016>12/31/2015)

我如何才能做到这一点。

谢谢!

Answer 1

这可能是您的解决方案：

CREATE TABLE #EntityAttributValue (entityID INT, Attribute VARCHAR(100),Value VARCHAR(100));
INSERT INTO #EntityAttributValue VALUES
 (1,'BirthDate','2000-04-01')
,(1,'Size','1.72')
,(1,'FirstName','John')
,(1,'LastName','Doe')
,(2,'BirthDate','1990-04-01')
,(2,'Size','1.81')
,(2,'FirstName','Jane')
,(2,'LastName','Miller')
,(3,'BirthDate','1980-05-01')
,(3,'FirstName','Hugo')
,(3,'LastName','Boss');

DECLARE @columns VARCHAR(MAX)=
(
    SELECT STUFF(
    (
    SELECT DISTINCT ',[' + Attribute + ']' 
    FROM #EntityAttributValue
    FOR XML PATH('')
    ),1,1,'')

);

DECLARE @query VARCHAR(MAX)=
'SELECT p.*
FROM
(
    SELECT *
    FROM #EntityAttributValue
) AS tbl
PIVOT
(
    MIN(Value) FOR Attribute IN(' + @columns + ')
) AS p';

--The column's list might be generated from metadata (dictionary attrib/type)
DECLARE @Wrapped VARCHAR(MAX)=
';WITH MyCTE AS (' +  @query + ')
SELECT  entityID
       ,CAST(BirthDate AS DATE) AS BirthDate
       ,FirstName
       ,LastName
       ,CAST(Size AS FLOAT) AS Size
FROM MyCTE';

EXEC (@Wrapped);

GO
DROP TABLE #EntityAttributValue;

这是以前的答案。

名称-值对(或实体-属性-值表EAV)通常是不应该做的事情……这里有一篇文章(你会发现更多！)描述原因：http://www.sturnus.co.uk/performance/2008-07/the-curse-of-the-name-value-pair/

如果您必须坚持这种设计，有一个解决方案是使用带有GROUP BY的CASE，但您必须确保您的值将被正确转换(特别要注意日期-时间值！)：

DECLARE @EntityAttributValue TABLE(entityID INT, Attribute VARCHAR(100),Value VARCHAR(100));
INSERT INTO @EntityAttributValue VALUES
 (1,'BirthDate','2000-04-01')
,(1,'Size','1.72')
,(1,'FirstName','John')
,(1,'LastName','Doe')
,(2,'BirthDate','1990-04-01')
,(2,'Size','1.81')
,(2,'FirstName','Jane')
,(2,'LastName','Miller')
,(3,'BirthDate','1980-05-01')
,(3,'FirstName','Hugo')
,(3,'LastName','Boss');

SELECT eav.entityID
      ,MAX(CASE WHEN eav.Attribute='FirstName' THEN eav.Value ELSE NULL END) AS FirstName
      ,MAX(CASE WHEN eav.Attribute='LastName' THEN eav.Value ELSE NULL END) AS LastName
      ,MAX(CASE WHEN eav.Attribute='BirthDate' THEN CAST(eav.Value AS DATE) ELSE NULL END) AS Birtdate
      ,MAX(CASE WHEN eav.Attribute='Size' THEN CAST(eav.Value AS FLOAT) ELSE NULL END) AS Size
FROM @EntityAttributValue AS eav
GROUP BY eav.entityID