I'm trying to pivot a single table that contains 7 columns but I really only need 2 of them. One column is called ID and the other is PROFILE_ID which is the one I want to transpose/pivot into 7 new columns based on the values originally returned. I know I can use a union but I'm trying to simply it if when I have other scenarios similar to this that have more values like 27 that needs to get transposed/pivoted. I tried a pivot syntax but it didn't seem to work for me or at least I'm doing it wrong. I only need the 'ID' and 'PROFILE_ID' column from the original table that needs to be pivoted or transposed. Below is an example of the original output and what I need or expect to get. Thanks in advance!
Original Output from select * dbo.Table
Need Output in this Format