How do I include the id column in a unpivot query

Question

I have a simple query that returns this result

TransactionIdentifier  TransactionType   DateTimeStamp
1                      Destruction       Dec 23 2015  7:43AM
2                      Management        May 21 2013  2:01AM

I want to convert it into the following format

TransactionIdentifier  ColumnName             Value
1                      TransactionIdentifier  1
1                      TransactionType        Destruction                                       
1                      DateTimeStamp          Dec 23 2015  7:43AM
2                      TransactionIdentifier  2
2                      TransactionType        Management                                        
2                      DateTimeStamp          May 21 2013  2:01AM

This is my query so far

   SELECT V.ColumnName, V.Value
    FROM (SELECT CONVERT( VARCHAR(4000), TransactionIdentifier) AS TransactionIdentifier,
                 CONVERT( VARCHAR(4000), TransactionType) AS TransactionType,
                 CONVERT( VARCHAR(4000), DateTimeStamp) AS DateTimeStamp
          FROM Transactions
          WHERE TransactionIdentifier IN(1, 2)) AS A1 
         UNPIVOT(Value FOR ColumnName IN(TransactionIdentifier,TransactionType,DateTimeStamp)) AS V

It returns this

ColumnName             Value
TransactionIdentifier  1
TransactionType        Destruction                                       
DateTimeStamp          Dec 23 2015  7:43AM
TransactionIdentifier  2
TransactionType        Management                                        
DateTimeStamp          May 21 2013  2:01AM

How can I add the TransactionIdentifier field to the result?

Answer 1

Just remove TransactionIdentifier from the unpivot and add it to the Select statement

SELECT TransactionIdentifier ,V.ColumnName, V.Value
    FROM (SELECT CONVERT( VARCHAR(4000), TransactionIdentifier) AS TransactionIdentifier,
                 CONVERT( VARCHAR(4000), TransactionType) AS TransactionType,
                 CONVERT( VARCHAR(4000), DateTimeStamp) AS DateTimeStamp
      FROM Transactions
      WHERE TransactionIdentifier IN(1, 2)) AS A1 

     UNPIVOT(Value FOR ColumnName IN(TransactionType,DateTimeStamp)) AS V

Output:

TransactionIdentifier   ColumnName          Value
1                       TransactionType     Destruction
1                       DateTimeStamp       Dec 23 2015  7:43AM
2                       TransactionType     Management
2                       DateTimeStamp       May 21 2013  2:01AM

Hope that helps.