Jim Horn
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:
Question: Is there any way in a PIVOT to do an 'all values' for columns?
Thanks in advance.
Jim
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
ASKER