Pivot table in ms sql

-- Sample data
declare @T table
(
  ID int,
  Title nvarchar(10),
  FieldsXml nvarchar(max)
)
insert into @T values
(1,     'A',       '<Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>'),
(2,     'B',       '<Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>'),
(3,     'C',       '<Fields><Field Name="Z">z3</Field></Fields>')

-- Create temp table
select T.ID,
       T.Title,
       TN.X.value('@Name', 'nvarchar(128)') as FieldName,
       TN.X.value('.', 'nvarchar(max)') as FieldValue
into #tmp      
from @T as T    
  cross apply (select cast(FieldsXml as XML)) as TX(X)
  cross apply TX.X.nodes('/Fields/Field') as TN(X)


declare @ColList nvarchar(max)
declare @Sql nvarchar(max)

-- Build column list
select @ColList = stuff((select '], ['+FieldName
                         from #tmp
                         group by FieldName
                         for xml path('')), 1, 2, '')+']'
select * from  #tmp
-- Build query
set @Sql = 'select *
            from (select ID,
                         Title,
                         FieldName,
                         FieldValue
                  from #tmp
                 ) as T
            pivot (min(FieldValue) for FieldName in (' + @ColList + ')) as P'

exec (@Sql)

drop table #tmp