I suspect there might be a really nice approach using PIVOT
, but I don't know it well enough to be able to say for sure. What I offer here works. I have split it up into chunks for better formatting and to provide commentary:
To start with let's capture the example data
-- Sample data
DECLARE @x3 xml
SET @x3 = '
<root>
<row>
<column>row 1 col 1</column>
<column>row 1 col 2</column>
<column>row 1 col 3</column>
</row>
<row>
<column>row 2 col 1</column>
<column>row 2 col 2</column>
<column>row 2 col 3</column>
</row>
<row>
<column>row 3 col 1</column>
<column>row 3 col 2</column>
<column>row 3 col 3</column>
</row>
</root>
'
DECLARE @x xml
SET @x = @x3
-- @x is now our input
Now the actual transposing code:
Establish the size of the matrix:
WITH Size(Size) AS
(
SELECT CAST(SQRT(COUNT(*)) AS int)
FROM @x.nodes('/root/row/column') T(C)
)
Shred the data, use ROW_NUMBER
to capture the index (the -1
is to make it zero based), and use modulo and integer divide on the index to work out the new row and column numbers:
,Flattened(NewRow, NewCol, Value) AS
(
SELECT
-- i/@size as old_r, i % @size as old_c,
i % (SELECT TOP 1 Size FROM Size) AS NewRow,
i / (SELECT TOP 1 Size FROM Size) AS NewCol,
Value
FROM (
SELECT
(ROW_NUMBER() OVER (ORDER BY C)) - 1 AS i,
C.value('.', 'nvarchar(100)') AS Value
FROM @x.nodes('/root/row/column') T(C)
) ShreddedInput
)
With this CTE FlattenedInput
available, all we now need to do is get the FOR XML
options and query structure right and we're done:
SELECT
(
SELECT Value 'column'
FROM
Flattened t_inner
WHERE
t_inner.NewRow = t_outer.NewRow
FOR XML PATH(''), TYPE
) row
FROM
Flattened t_outer
GROUP BY NewRow
FOR XML PATH(''), ROOT('root')
Sample output:
<root>
<row>
<column>row 1 col 1</column>
<column>row 2 col 1</column>
<column>row 3 col 1</column>
</row>
<row>
<column>row 1 col 2</column>
<column>row 2 col 2</column>
<column>row 3 col 2</column>
</row>
<row>
<column>row 1 col 3</column>
<column>row 2 col 3</column>
<column>row 3 col 3</column>
</row>
</root>
Works on any size 'square' data. Note the lack of sanity checking / error handling.