SQL Table Rows to Columns - Possible PIVOT?

Question

I have a table of information from Active Direcory, but unfortunately it's displayed like this;

+------+------------+----------------+-------------------+
| dnId | propNameId |  propertyName  |   propertyValue   |
+------+------------+----------------+-------------------+
|    1 |         10 | objectsid      | S-1-5-32-548      |
|    1 |         19 | _objectclass   | group             |
|    1 |         80 | cn             | Account Operators |
|    1 |         82 | samaccountname | Account Operators |
|    1 |         85 | name           | Account Operators |
|    2 |         10 | objectsid      | S-1-5-32-544      |
|    2 |         19 | _objectclass   | group             |
|    2 |         80 | cn             | Administrators    |
|    2 |         82 | samaccountname | Administrators    |
|    2 |         85 | name           | Administrators    |
|    3 |         10 | objectsid      | S-1-5-32-551      |
|    3 |         19 | _objectclass   | group             |
|    3 |         80 | cn             | Backup Operators  |
|    3 |         82 | samaccountname | Backup Operators  |
|    3 |         85 | name           | Backup Operators  |
+------+------------+----------------+-------------------+

Using a bunch of temp tables and joins, I can get it to display how I want, like this;

+------+--------------+--------------+-------------------+-------------------+-------------------+
| dnId |  objectsid   | _objectclass |        cn         |  samaccountname   |       name        |
+------+--------------+--------------+-------------------+-------------------+-------------------+
|    1 | S-1-5-32-548 | group        | Account Operators | Account Operators | Account Operators |
|    2 | S-1-5-32-544 | group        | Administrators    | Administrators    | Administrators    |
|    3 | S-1-5-32-551 | group        | Backup Operators  | Backup Operators  | Backup Operators  |
+------+--------------+--------------+-------------------+-------------------+-------------------+

But this feels really messy and long-winded.. I'm sure there must be a more elegant way of doing this, perhaps using PIVOT, which I've tried (and failed) to understand.

Any thoughts/suggestions?

EDIT: Apologies, removed mysql tag (it was auto-suggested). I'm using MS SQL Server 2008 R2.

here's a [answer](http://stackoverflow.com/a/14317700/1585003) for similar question. — Ravi Teja N, Aug 04 '14 at 16:15

score 5 · Answer 1 · answered Aug 04 '14 at 16:35

You did not specify what database you are using but the following will work in either SQL Server or MySQL. You can create your new column names by using an aggregate function with a CASE expression:

select
  dnid,
  max(case when propertyName = 'objectsid' then propertyValue end) objectsid,
  max(case when propertyName = '_objectclass ' then propertyValue end) objectclass ,
  max(case when propertyName = 'cn' then propertyValue end) cn,
  max(case when propertyName = 'samaccountname' then propertyValue end) samaccountname,
max(case when propertyName = 'name' then propertyValue end) name
from yourtable
group by dnid;

See SQL Fiddle with Demo

Lukas Eder · Accepted Answer · 2014-08-04T16:34:11.083

3

If you're using SQL Server (not sure from your tags), this query will generate the result you're looking for, using PIVOT

SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue FROM myTable
) AS t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (objectsid, _objectclass, cn, samaccountname, name)
) AS p;

See a SQL Fiddle here

edited Aug 04 '14 at 16:34

answered Aug 04 '14 at 16:11

Lukas Eder

211,314
129
689
1,509

This did the trick, thanks! Apologies for mysql tag, it was auto-suggested and I blindly clicked it. Will be more careful in future. – Valiante Aug 05 '14 at 08:38

SQL Table Rows to Columns - Possible PIVOT?

2 Answers2