Hi, I have a requirement to convert columns to rows in a dimension table load process.
I have put together the following from stackoverflow
https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows
;with CTE1 as (
select
t.booking,
t.branch_code,
t.company,
t.trans_number,
t.trip,
(select t.* for xml raw('row'), type) as Data
from My_Table as t
), CTE2 as (
select
C.booking, C.branch_code,c.trip,c.company,c.trans_number,
F.C.value('local-name(.)', 'nvarchar(128)') as IndicatorName,
F.C.value('.', 'nvarchar(max)') as IndicatorValue
from CTE1 as c
outer apply c.Data.nodes('row/@*') as F(C)
)
select * from CTE2
The bottom line shows c.data.nodes as an invalid object.
I am wondering if I have a syntax error or is it that 2008 R2 can't handle it?
Any ideas gratefully received.
Dave