19 lines
590 B
SQL
19 lines
590 B
SQL
SELECT * FROM (
|
|
SELECT FormatID,Name,ParentID
|
|
,isnull(v.value('@TabFormat', 'varchar(100)'),'*') AS [MyFormat]
|
|
,v.value('@Index', 'Int') AS [MyIndex]
|
|
From Formats
|
|
CROSS APPLY Data.nodes('//SeqTabFmt') TempXML(v)
|
|
) AS SRC
|
|
PIVOT( Max(MyFormat) FOR MyIndex IN (
|
|
[0], [1],[2],[3],[4],[5],[6],[7],[8]
|
|
)) AS PVT1 Order by FormatID
|
|
|
|
|
|
SELECT FormatID,Name,Description,ParentID, v.query('../..') parentNode
|
|
,isnull(v.value('@TabFormat', 'varchar(100)'),'*') AS [MyFormat]
|
|
,v.value('@Index', 'Int') AS [MyIndex]
|
|
From Formats
|
|
CROSS APPLY Data.nodes('//SeqTabFmt') TempXML(v)
|
|
|
|
select * from formats |