CODEWITHSUNDEEP
sqlsql-server
Rise_against
Rise_against

Reputation: 1060

t-sql transpose rows to columns

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!

Upvotes: 0

Views: 642

Answers (2)

jpw
jpw

Reputation: 44871

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

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

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       ║
╚══════════════════════════════════════╩═════════════════════════╩═══════════╩════════════════╝

Upvotes: 3

Related Questions