StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I don't think CTE's are the complete answer to your problem. What you're after is a PIVOT query where the number of columns in the PIVOT are unknown at query time. This question and answer looks like what you're after:</p> <p><a href="https://stackoverflow.com/questions/198716/pivot-in-sql-2005">PIVOT in sql 2005</a></p> <p><a href="https://stackoverflow.com/questions/198716/pivot-in-sql-2005#199763">PIVOT in sql 2005</a></p> <p>From the example in the above answer, this is the SQL modified for your table (which I've named 'q395075' - so you just need to replace with your table name):</p> <pre><code>DECLARE @sql AS varchar(max) DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']' ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']' FROM ( SELECT DISTINCT PIVOT_CODE FROM ( SELECT DocNum, DocEntry, ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY DocEntry) AS PIVOT_CODE FROM q395075 ) AS rows ) AS PIVOT_CODES SET @sql = ' ;WITH p AS ( SELECT DocNum, DocEntry, ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY DocEntry) AS PIVOT_CODE FROM q395075 ) SELECT DocNum, ' + @select_list + ' FROM p PIVOT ( MIN(DocEntry) FOR PIVOT_CODE IN ( ' + @pivot_list + ' ) ) AS pvt ' PRINT @sql EXEC (@sql) </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