t-sql transpose rows to columns

Question

I am having some issues creating a query where I kind of transpose the rows as columns. For example, I have following table:

UniqueId                               | PropertyName   | PropertyValue  | Time
--------------------------------------------------------------------------------------------------
EA91B396-A3DE-4A16-850B-30D7CD45D753   | FileName       | Test.txt       | 2014-09-26 19:12:58.203
EA91B396-A3DE-4A16-850B-30D7CD45D753   | SourceLocation | C:\Temp        | 2014-09-26 19:12:58.203
1036E17B-3527-4F26-9ABD-565DF98C7A98   | FileName       | Test2.txt      | 2014-09-26 19:15:02.215
1036E17B-3527-4F26-9ABD-565DF98C7A98   | SourceLocation | C:\Temp2       | 2014-09-26 19:15:02.215

Now I would like to transpose this data into the following table:

UniqueId                               | FileName       | SourceLocation  | Time
------------------------------------------------------------------------------------------------
EA91B396-A3DE-4A16-850B-30D7CD45D753   | Test.txt       | C:\Temp         | 2014-09-26 19:12:58.203
1036E17B-3527-4F26-9ABD-565DF98C7A98   | Test2.txt      | C:\Temp2        | 2014-09-26 19:15:02.215

So basically each value of "PropertyName" becomes a column in my output query and the "PropertyValue" column becomes the value of that new column. Any idea how I can accomplish this?

Thanks in advance!

Google: "sql server pivot". If you don't know the list of all the properties, through "dynamic" in there. — Gordon Linoff, Sep 28 '14 at 12:13

score 3 · Accepted Answer · answered Sep 28 '14 at 12:27

Query

SELECT * 
FROM Table_Name T 
            PIVOT (
                   MAX(PropertyValue)
                   FOR PropertyName
                   IN ([FileName],[SourceLocation])
                   )p

Result

╔══════════════════════════════════════╦═════════════════════════╦═══════════╦════════════════╗
║               UniqueId               ║          Time           ║ FileName  ║ SourceLocation ║
╠══════════════════════════════════════╬═════════════════════════╬═══════════╬════════════════╣
║ EA91B396-A3DE-4A16-850B-30D7CD45D753 ║ 2014-09-26 19:12:58.203 ║ Test.txt  ║ C:\Temp        ║
║ 1036E17B-3527-4F26-9ABD-565DF98C7A98 ║ 2014-09-26 19:15:02.217 ║ Test2.txt ║ C:\Temp2       ║
╚══════════════════════════════════════╩═════════════════════════╩═══════════╩════════════════╝

score 1 · Answer 2 · answered Sep 28 '14 at 12:30

1

If you know what the PropertyName values will be you can use PIVOT and hard code the values:

SELECT [UniqueId], [FileName], [SourceLocation], [Time] 
FROM (
    SELECT [UniqueId], [PropertyName], [PropertyValue], [Time] 
    FROM Table1 
    ) SourceTable
PIVOT (
    MAX(PropertyValue) FOR PropertyName IN ([FileName], [SourceLocation])
) AS PivotedTable

If the PropertyName values can vary you need to use dynamic SQL to build a list of properties for the PIVOT:

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @COLS AS NVARCHAR(MAX)

SELECT @COLS= ISNULL(@COLS + ',','') + QUOTENAME(PropertyName)
FROM (SELECT DISTINCT PropertyName FROM Table1) AS Properties

SET @SQL =
  N'SELECT [UniqueId], ' + @COLS + ', [Time]
    FROM Table1
    PIVOT (
       MAX(PropertyValue) FOR PropertyName IN (' + @COLS + ')
       ) AS PivotedTable'

EXEC sp_executesql @SQL

Sample SQL Fiddle

answered Sep 28 '14 at 12:30

jpw

44,361
6
66
86

There are only two values OP is looking for, why would you go through all this trouble just to Pivot 2 values :S – M.Ali Sep 28 '14 at 12:31
@M.Ali Guess I didn't read the question well enough to know that there would always be just two values. In any case it won't hurt to show the alternative procedure in my opinion, maybe it might be useful for the OP in the future if more properties are added. Plus I already had the skeleton code, so adapting it wasn't much work ;) – jpw Sep 28 '14 at 12:33
1

Thx jpw, good to know the solution for dynamic values as well :) There won't be more than 2 different values, so I opted for the solution of M. Ali. – Rise_against Sep 28 '14 at 12:47

t-sql transpose rows to columns

2 Answers2

Query

Result

Linked