x

How to transform vertical table into horizontal table?

Hello,

I have one table Person:

Id Name
1  Person1
2  Person2
3  Person3

And I have its child table Profile:

Id PersonId FieldName Value
1  1        Firstname Alex
2  1        Lastname Balmer
3  1        Email some_email@test.com
4  1        Phone +1 2 30004000

And I want to get data from these two tables in onw row like this:

Id Name     Firstname Lastname  Email                Phone 
1  Person1  Alex      Balmer    some_email@test.com  +1 2 30004000
  1. What is the most optimized query to get these vertical (key, value) values in one row like this? Now I have a problem that I done four joins of child table to parent table because I need to get these four fields. Some optimization is for sure possible.
  2. I would like to be able to modify this query in easy way when I add new field (key,value). What is the best way to do this? To create some StoreProcedure?

I would like to have strongly types in my DB layer (C#) and using LINQ (when programming) so it means when I add some new Key, Value pair in Profile table I would like to do minimal modifications in DB and C# if possible. Actually I am trying to get some best practises in this case.

more ▼

asked Apr 15, 2010 at 06:19 AM in Default

Aleksandar gravatar image

Aleksandar
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can use the PIVOT operator for this.

Here you have an example for your task:

DECLARE @person TABLE (
    Id int,
    Name varchar(10)
)

INSERT INTO @person (id, Name)
SELECT 1, 'Person1' UNION ALL
SELECT 2, 'Person2' UNION ALL
SELECT 3, 'Person3' UNION ALL
SELECT 4, 'Person4'

DECLARE @profile TABLE (
    Id int,
    PersonId int,
    FieldName varchar(50),
    Value varchar(50)
)

INSERT INTO @profile (Id, PersonID, FieldName, Value)
SELECT 1, 1, 'Firstname', 'Alex' UNION ALL
SELECT 2, 1, 'Lastname', 'Balmer' UNION ALL
SELECT 3, 1, 'Email', 'some_email@test.com' UNION ALL
SELECT 4, 1, 'Phone', '+1 2 30004000'

;WITH PivotTable AS (
    SELECT 
        PersonId,
        Firstname,
        Lastname,
        Email,
        Phone
    FROM
    (
        Select 
            PersonId,
            FieldName,
            Value
        From @profile
    ) p
    PIVOT
    (
        MIN(Value)
        FOR FieldName IN ([Firstname], [Lastname], [Email], [Phone])
    ) AS pvt
)
SELECT
    P.Id,
    P.Name,
    PT.Firstname,
    PT.Lastname,
    PT.Email,
    PT.Phone
FROM PivotTable PT
INNER JOIN @person P ON PT.PersonID = P.ID

For more inforamtion look on MSDN

more ▼

answered Apr 15, 2010 at 07:34 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

First, it would be a big help to everyone if you posted data in a readily consumable format. That includes table creation statments. Pavel took the time to do that and I'll do it as well this time just to show another method. It almost guarantees that you'll get a coded/tested answer and it identifies the data types we need to work with. Here's the test setup code...

--===== Work in a nice safe place that everyone has...
    USE TempDB
;
--===== Create and populate the Person table
 CREATE TABLE dbo.Person 
        (Id INT, Name VARCHAR(30))
;
 INSERT INTO dbo.Person  
        (Id,Name)
 SELECT 1,'Person1' UNION ALL
 SELECT 2,'Person2' UNION ALL
 SELECT 3,'Person3'
;
--===== Create and populate the Profile table
 CREATE TABLE dbo.Profile
        (Id INT, PersonId INT, FieldName VARCHAR(50), Value VARCHAR(100))
 INSERT INTO dbo.Profile
        (Id,PersonId,FieldName,Value)
 SELECT 1,1,'Firstname','Alex' UNION ALL
 SELECT 2,1,'Lastname','Balmer' UNION ALL
 SELECT 3,1,'Email','some_email@test.com' UNION ALL
 SELECT 4,1,'Phone','+1 2 30004000'
;

Here's another way to do the same thing Pavel did for an answer... it's called a "CROSS TAB" and is usually just a bit faster than a pivot...

--===== Now, solve the problem using a nice little CROSS TAB
 SELECT person.ID,
        person.Name,
        MAX(CASE WHEN profile.FieldName = 'FirstName' THEN Value END) AS FirstName,
        MAX(CASE WHEN profile.FieldName = 'Lastname'  THEN Value END) AS LastName,
        MAX(CASE WHEN profile.FieldName = 'Email'     THEN Value END) AS Email,
        MAX(CASE WHEN profile.FieldName = 'Phone'     THEN Value END) AS Phone
   FROM dbo.Person person
  INNER JOIN dbo.Profile profile
     ON person.ID = profile.PersonID
  GROUP BY person.ID, person.Name

Please see the following article for why... http://www.sqlservercentral.com/articles/T-SQL/63681/

more ▼

answered Apr 19, 2010 at 02:32 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x107
x33

asked: Apr 15, 2010 at 06:19 AM

Seen: 10858 times

Last Updated: Apr 15, 2010 at 06:19 AM