I was given a table that has 3 fields. One is ID, but unlike most tables, it allows for duplicate IDs in that field. It is 3 fields, but millions of records. It has ID, Year, and Category. What I need is to be able to pivot this table. What is the best tool or most efficient way to get each unique ID with each Category-Year as a column?
How may pairs do you have? The number of columns in a result set is limited to 4096.
Much less than that.
you could do it with dynamic sql but it won't be pretty or else use for xml path('') and concatenate things as strings
Is there not a tool that can handle pivots quickly for a lot of rows?
Lots of input rows, sure, just not lots of columns in the result set
Ok, which tool will do it quickly? Toad is too slow. Thus, that is why I am thinking about having to reformat the table. Just looking for advice on how to get what I need from the current table.
SELECT id , stuff(( SELECT ', ' + category + '-' + cast(yr AS CHAR(4)) FROM mytable mt_inner WHERE mt.id = mt_inner.id ORDER BY category , yr FOR XML path('') ), 1, 2, '') AS mystuff FROM mytable mt GROUP BY id