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.

Answer 1

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

Answer 2

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