Transpose some rows of data into next columns in SQL Server 2008

Question

I have a requirement to transpose only few row into additional columns in SQL server 2008

Source table data

Test    Cmdid   Test Name   Test Value
TC1     1        TN1         A
TC1     2        TN1         B
TC1     3        TN1         C
TC1     4        TN1         D

TC1     1        TN2         X
TC1     2        TN2         Y
TC1     3        TN2         Z
TC1     4        TN2         M

This data should get transposed as below

Test    Cmdid   TN1Values   TN2Values
TC1      1       A           X
TC1      2       B           Y
TC1      3       C           Z
TC1      4       D           M

How can I transpose the data?

Answer 1

Unless I'm missing something here this looks like an ordinary pivot query:

SELECT [Test], [Cmdid], [TN1] AS TN1Values, [TN2] AS TN2Values
FROM Table1
PIVOT (
    MAX([Test Value]) FOR [Test Name] IN ([TN1],[TN2])
) pvt

Sample SQL Fiddle