PIVOT DINÁMICO USANDO CTE, WITH

-- ===================================================================

-- Autor:                 MANUEL OMAR OLGUÍN HERNÁNDEZ

-- Fecha:                 2020 MAYO 8

-- Versión:                1.0

-- Requerimiento:   PIVOT TABLE FORMED USING XML

-- Descripcion:            PIVOT TABLE sin necesidad de especificar explicitamente los nombres de las columnas

-- ================================================================

Select ID = 1, 'Tom' as Name ,'Bombadill' as Surname ,99999 as Age ,'Withywindle' as Address

UNION ALL

Select ID = 2, 'OMAR' as Name ,'OLGUIN' as Surname ,40 as Age ,'MEXICO' as Address

;with SampleCTE

as

(

Select ID = 1, 'Tom' as Name ,'Bombadill' as Surname ,99999 as Age ,'Withywindle' as Address

UNION ALL

Select ID = 2, 'OMAR' as Name ,'OLGUIN' as Surname ,40 as Age ,'MEXICO' as Address

)

Select A.ID, c.*

From SampleCTE A

Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)

Cross Apply (

                    Select       Item = a.value('local-name(.)','varchar(100)') ,Value = a.value('.','varchar(max)')

                    From         B.XMLData.nodes('/row') as C1(n)

                    Cross Apply C1.n.nodes('./@*') as C2(a)

                    Where a.value('local-name(.)','varchar(100)') not in ('ID','ExcludeOtherCol')

                    ) C