SQL Server Transpose FieldValue as FieldName

SQL Server Transpose , FieldValues as FieldNames:

Hello All, I have a tricky request for all of you.

I have a table with the following data

Field1  Field2  Field3                      Field4
----------------------------------------------------
KEY1    01      FieldName1                  FieldValue1
kEY1    01      FieldName2                  FieldValue2
KEY1    01      FieldName3                  FieldValue3
KEY1    01      FieldName4                  FieldValue4

I can use pivot to transpose, but I want the result to be

Filed1  Field2  FieldName1   FieldName2   FieldName3     FieldName4   
--------------------------------------------------------------------
KEY1    01      FieldValue1  FieldValue2  FieldValue3    FieldValue4    

Is it possible to achieve this using pivot without using XML ?

Thank you in advance.

Answer

You can try to use condition aggregate function

SELECT Field1,
       Field2,
       MAX(CASE WHEN Field3 = 'FieldName1' THEN Field4 END) FieldName1,
       MAX(CASE WHEN Field3 = 'FieldName2' THEN Field4 END) FieldName2,
       MAX(CASE WHEN Field3 = 'FieldName3' THEN Field4 END) FieldName3,
       MAX(CASE WHEN Field3 = 'FieldName4' THEN Field4 END) FieldName4
FROM T
GROUP BY Field1,Field2

SQLFIDDLE

if you want to create your columns dynamically you can try to use dynamical pivot.

create your SQL statement and make condition aggregate function by connecting SQL string. then use execute it Dynamically.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ', MAX(CASE WHEN Field3 = ''' + Field3+''' THEN Field4 END) ' + QUOTENAME(Field3) 
            FROM T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET  @query= 'SELECT Field1,
       Field2, '+ @cols+ ' 
FROM T
GROUP BY Field1,Field2';

execute(@query)

sqlfiddle

Result

Filed1  Field2  FieldName1   FieldName2   FieldName3     FieldName4   
--------------------------------------------------------------------
KEY1    01      FieldValue1  FieldValue2  FieldValue3    FieldValue4