How can I create multiple columns from one DB Field in SQL Server?

Question

I have a field called PropertyValue in the UserProfile table that can contain information for address, phone number, first name, last name, user name, city, etc... each record in this table is associated to a user by UserId, it is also associated to a ProfilePropertyDefinition which contains the definition for each of the properties (ie. PropertyName).

Through this relationship I can get all of the property values along with their property names. What I would like to do it to extract the data from these two columns (PropertyValue, PropertyName) and create a table similar to this:

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

So, I wanted to know if I can use a SQL statement to do this, here's my go at it:

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'))

But that didn't work and something seems really weird about it... Anyone know how to take one column and display it's values in several different columns?

Answer 1

I guess you could do a select from the same table multiple times. Let say tA is the name table with UserProfileID,PropertyDefinition and PropertyValue

You could do

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 ....

Not ideal, but it would work

Answer 2

You need to join the tables many times over (as many times as you have fields):

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'
...

Note that this relies on their being some ID field in the UserProfile that you can use to tie all the rows for the same user together.

Answer 3

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 4

Assuming schema like

UserProfile#
{userid,
ProfileName, 
propertyValue
}

You would want to do

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 5

I would write the query like this:

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 6

I would need to know more about your table sturtures and what you are trying to achomplish but an option may be to create a SQL Scalar Function to retieve the values of the properties. I am making some assumptions on table names and database setup but try this on...

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]

Answer 7

Personally, I would stop right now and consider how bad this design will be for performance. This is in general a very poor technique to use to store this type of data. If you have 20 proerties you want to display you will have to join (And left join at that as you can't guarantee each property will be represented) to this table 20 times. Further, if this is central to your data structure (As it sounds like it is from the type of data you seem to be storing) virtually every query will need to do something simliar and performance will be atrocious. There are time when this is the best model (when you have no way of knowing in advance what properties will need to be stored), but most of the time, it's use is a sign of a bad design.

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