How to convert row data into columns in SQL

Question

I have looked into pivot but I think it requires an aggregate function which I do not need (I think).

The result of my query is this

    Name          Property Name        PropertyValue
   ----------      ----------            ----------
     lorem          Work Phone         000.111.2020
     ipsum          Email              [email protected]

To

Name          Work Phone                Email
----------    ----------            ----------
lorem        000.111.2020        [email protected]
ipsum        001.101.2010        [email protected]

I don't think I should use pivot here because I don't need to aggregate anything, I just want the row data to become a column.

OMG Ponies · Accepted Answer · 2010-06-07 21:50:17Z

3

Use:

  SELECT t.name, 
         MAX(CASE WHEN t.property = 'Work Phone' THEN t.value ELSE NULL END),
         MAX(CASE WHEN t.property = 'Email' THEN t.value ELSE NULL END)
    FROM TABLE t
GROUP BY t.name

You have to use aggregate functions, otherwise you'd have multiple rows with NULLs in place.

answered Jun 7, 2010 at 21:50

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cade Roux · Accepted Answer · 2010-06-07 21:56:21Z

PIVOT requires an aggregate, and yes, you would need an aggregate if your data was:

Name          Property Name        PropertyValue 
   ----------      ----------            ---------- 
     lorem          Work Phone         000.111.2020 
     lorem          Work Phone         999.999.9999
     ipsum          Email              [email protected]

Given that you know your data is unique, you can just use MIN or MAX in your pivot.

NB: Your example output doesn't match your exmaple input.

http://cloudexchange.cloudapp.net/stackoverflow/q/2589

-- SO2993412

DECLARE @t AS TABLE (Name varchar(25), [Property Name] varchar(25), PropertyValue varchar(25))
INSERT INTO @t VALUES ('lorem', 'Work Phone', '000.111.2020')
    ,('ipsum', 'Email', '[email protected]')

SELECT Name, [Work Phone], [Email]
FROM @t
PIVOT (MAX(PropertyValue) FOR [Property Name] IN ([Work Phone], [Email])) AS pvt

Collectives™ on Stack Overflow

How to convert row data into columns in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related