ms sql pivot does not work

Question

I'm trying to transpose a simple table from rows to columns with two string variables. I went trough several examples from the web without success. The number of rows will vary so I need to transpose the table dynamically. The following code at least does not produce an error but does not generate the result!

The sample table

    create table #Encabezado
    (
    NodeName nvarchar(100),
    NodeValue nvarchar(100)
    )

INSERT INTO #Encabezado (NodeName, NodeValue) VALUES
('RUTEmisor','88888888-8'),
('RznSoc','EMPRESA DE PRUEBA'),
('GiroEmis','Informatica'),
('Acteco','1'),
('CdgSIISucur','59529595'),
('DirOrigen','Teatinos 120'),
('CmnaOrigen','Santiago'),
('CiudadOrigen','Santiago')
GO

The unpivot code

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = '#Encabezado' and
                 C.column_name like '%Name'
           for xml path('')), 1, 1, '')

set @query 
  = 'select NodeName,
        Nodevalue
     from #Encabezado
     unpivot
     (
        NodeName
        for NodeName in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

Any help would be appreciated

Answer 1

Sequelspear once said : "To Pivot or UnPivot, That's the question."

declare @cols NVARCHAR(MAX) = stuff((select ','+quotename(Nodename) from #Encabezado group by Nodename for xml path('')),1,1,'');

declare @query NVARCHAR(MAX) = 'select * from #Encabezado pivot (max(NodeValue) for NodeName IN ('+ @cols +')) pvt';

exec sp_executesql @query;