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

avatar 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

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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

avatar image

Jeff Moden
1.9k 3 7 12

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

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:

x2018
x76
x42

asked: Apr 15, 2010 at 06:19 AM

Seen: 15354 times

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

Copyright 2016 Redgate Software. Privacy Policy