StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you want to include the <code>sequence</code> number as part of your column names, then you will still need to unpivot your <code>col1</code> and <code>col2</code> columns first, then apply the pivot. The difference is that you will concatenate the <code>sequence</code> number to your column names created during the unpivot process.</p> <p>For a known number of values the query would be:</p> <pre><code>select REFID, [1col1], [2col1], [3col1], [1col2], [2col2], [3col2] from ( select REFID, col = cast(Sequence as varchar(10))+ col, value from yourtable cross apply ( select 'COL1', col1 union all select 'COL2', col2 ) c (col, value) ) d pivot ( max(value) for col in ([1col1], [2col1], [3col1], [1col2], [2col2], [3col2]) ) piv order by refid; </code></pre> <p>Then if you have an unknown number the dynamic SQL version will be:</p> <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(cast(Sequence as varchar(10))+ col) from yourtable cross apply ( select 'Col1', 1 union all select 'Col2', 2 ) c(col, so) group by Sequence, col, so order by so, sequence FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT refid, ' + @cols + ' from ( select REFID, col = cast(Sequence as varchar(10))+ col, value from yourtable cross apply ( select ''COL1'', col1 union all select ''COL2'', col2 ) c (col, value) ) x pivot ( max(value) for col in (' + @cols + ') ) p order by refid' execute sp_executesql @query; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload