SQL Pivot Table Analysis

1

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?

2

How may pairs do you have? The number of columns in a result set is limited to 4096.

3

Much less than that.

4

you could do it with dynamic sql but it won't be pretty or else use for xml path('') and concatenate things as strings

5

Is there not a tool that can handle pivots quickly for a lot of rows?

6

Lots of input rows, sure, just not lots of columns in the result set

7

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.

8 
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