如何从 SQL Server 中的一个数据库字段创建多个列？

Question

我在 UserProfile 表中有一个名为 PropertyValue 的字段，其中可以包含地址、电话号码、名字、姓氏、用户名、城市等信息......该表中的记录通过 UserId 与用户关联，它还与包含每个属性（即 PropertyName）的定义的 ProfilePropertyDefinition 关联。

通过这种关系，我可以获得所有属性值及其属性名称。 我想要做的是从这两列（PropertyValue、PropertyName）中提取数据并创建一个与此类似的表：

First Name | Last Name | Email | Phone | City | Country
-------------------------------------------------------
           |           |       |       |      |

所以，我想知道是否可以使用 SQL 语句来执行此操作，这是我的做法：

SELECT FirstName = (SELECT PropertyValue FROM UserProfile WHERE PropertyDefinitionID = (SELECT PropertyDefinitionID WHERE PropertyName = 'first name')), 
LastName = (SELECT PropertyValue FROM UserProfile WHERE PropertyDefinitionID = (SELECT PropertyDefinitionID WHERE PropertyName = 'last name')), 
Email = (SELECT PropertyValue FROM UserProfile WHERE PropertyDefinitionID = (SELECT PropertyDefinitionID WHERE PropertyName = 'email'))

但这不起作用，而且看起来真的很奇怪......有人知道如何获取一列并在几个不同的列中显示它的值吗？

Answer 1

SELECT fn.PropertyValue FirstName,
       ln.PropertyValue LastName,
       etc...

From UserProfile fN
   Join UserProfile lN
      On fN.PropertyName = 'first name' 
         And ln.PropertyName = 'last name' 
         And fn.user = ln.user
   Join UserProfile eM
      On fN.PropertyName = 'first name' 
         And eM.PropertyName = 'email' 
         And eM.user = fn.user

(

Answer 2

就我个人而言，我会立即停下来考虑一下这种设计对性能的影响有多大。 一般来说，用于存储此类数据的技术非常差。 如果您想要显示 20 个属性，则必须将该表加入（并保留左联接，因为您不能保证每个属性都会被表示）20 次。 此外，如果这是您的数据结构的核心（听起来像是您似乎存储的数据类型），那么几乎每个查询都需要执行类似的操作，并且性能将非常糟糕。 有时这是最好的模型（当您无法提前知道需要存储哪些属性时），但大多数时候，它的使用是糟糕设计的标志。

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

Answer 3

我想您可以多次从同一个表中进行选择。
假设 tA 是包含 UserProfileID、PropertyDefinition 和 PropertyValue 的名称表，

您可以这样做

select
t1.PropertyValue as FirstName,
t2.PropertyValue as LastName,
...
FROM
tA as t1, tA as t2, ....
WHERE
t1.PropertyDefinition Like 'FirstName' AND
t2.PropertyDefinition Like 'LastName' AND
....
AND
t1.UserId = @user AND
t2.UserID = @user ....

并不理想，但它会起作用

Answer 4

您需要多次连接表（与字段的数量一样多）：

SELECT UPFN.PropertyValue AS FirstName, UPLN.PropertyValue AS LastName, ...
FROM UserProfile UPFN
INNER JOIN ProfilePropertyDefinition PPDFN ON PPDFN.PropertyDefinitionID = UPFN.PropertyDefinitionID AND PPDFN.PropertyName = 'first name'
INNER JOIN UserProfile UPLN ON UPLN.id = UPFN.id
INNER JOIN ProfilePropertyDefinition PPDLN ON PPDLN.PropertyDefinitionID = UPLN.PropertyDefinitionID AND PPDLN.PropertyName = 'last name'
...

请注意，这依赖于它们是 UserProfile 中的某个 ID 字段，您可以使用该字段将同一用户的所有行绑定在一起。

Answer 5

假设像

UserProfile#
{userid,
ProfileName, 
propertyValue
}

您想要做的模式

SELECT 
 FirstName.PropertyValue  FirstNAme,
 LastName.PropertyValue  LastName,
FROM
users
JOIN (USERPROFILE) FirstName ON
FirstName.userid = users.userid
and PropertName ='FirstName'
JOIN (USERPROFILE) LastName ON
LastName.userid = users.userid
and PropertName ='LastName'

Answer 6

我会这样写查询：

Select 
  aa.userId,
  Coalesce(Max(Case when PropertyName = 'First Name' then PropertyValue else '' end),'') as FirstName,
  and so on
from
  UserTable as aa
left join
  UserProfile as bb
  on
  aa.UserId = bb.UserId
left join
  ProfilePropertyDefinition as cc
  on bb.PropertyDefinitionId = cc.PropertdefinitionId
group by
  aa.UserId

Answer 7

我需要更多地了解您的表结构以及您想要实现的目标，但一个选项可能是创建一个 SQL 标量函数来检索属性的值。 我对表名和数据库设置做了一些假设，但尝试一下......

CREATE FUNCTION [dbo].[UserProperty] 
(
    @UserProfileID UNIQUEIDENTIFIER, @Property VARCHAR(200)
)
RETURNS VARCHAR(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Value AS VARCHAR(MAX)
    SELECT @Value = PropertyValue FROM UserProfile up INNER JOIN PropertyDefinitions pd ON 
    up.PropertyDefinitionID = pd.PropertyDefinitionID 
    WHERE pd.PropertyName = @Property AND up.UserProfileID=@UserProfileID

    RETURN ISNULL(@Value,'')

END

SELECT 
    [dbo].[UserProperty](UserProfileID, 'first name') AS [First Name],
    [dbo].[UserProperty](UserProfileID, 'last name') AS [Last Name],
    [dbo].[UserProperty](UserProfileID, 'email') AS [Email] 
FROM 
    [Users]