Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

T-SQL PIVOT: Is there any way to do an 'all values' for columns instead of [1], [2], [3], ..., [200]

Hi All

I have a table my_table that has a row_id, column_id, and value.
There are 500 rows and 200 columns, so rows x columns = a rowcount of 100,000.

To flatten all rows, and the first three columns, the below T-SQL works great:
SELECT row_id, [1], [2], [3]
FROM (SELECT row_id, column_id, value FROM my_table WHERE some_fk_id = 0 AND column_id IN (1, 2, 3)) AS src
PIVOT ( MIN(value) FOR column_id IN ([1], [2], [3])) AS tgt

Open in new window

I have 200 columns, and would like to avoid hard-typing [1], [2], [3], ..., [200] in my T-SQL.
Question:  Is there any way in a PIVOT to do an 'all values' for columns?

Thanks in advance.
Jim
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

... playing with it ...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.