I've written a stored procedure that is returning a 2 column temp table, one ID column that is not unique, but a value between 2 and 12 to group values on. The other column is that actual data value column. I want to break out this 1 column table into a table into basically 1 table of 11 columns, 1 column for each dataset.
i'd like to have this parsed out into columns by ID. An identity column is not necessary since they will be unique to their own column. Something like;
Data2 Data3 Data4
102692... 103516.... 108408....
104114... 103476.... 108890....
and so on. I have tried using a While Loop through the datasets, but it's mainly getting these contained in 1 insert that is troubling me. I can't figure out how to say
While recordCount > 0
Insert into @tempTable(value1ID2,value1ID3,Value1ID4)
and then loop through value2ID2, value2ID3 etc.
If this isn't attainable that's fine i'll have to figure out a workaround, but the main reason i'm trying to do this is for a Report Builder dataset for a line chart that will eventually share a date grouping.
Since you need to aggregate string values, then you will need to use either the
min aggregate function. The problem with that is it will return a single row for each column. In order to rerun multiple rows, then you will need to use a windowing function like
row_number() to generate a unique value for each
string combination. This will allow you to return multiple rows for each
select Data2 = , Data3 = , Data4 =  from ( select id, stringvalue, row_number() over(partition by id order by stringvalue) seq from yourtable ) d pivot ( max(stringvalue) for id in (, , ) ) piv