I have a function that produces a few rows as string values. The values are as follows:
[12*02]
[12*03]
[12*04]
[12*05]
[12*06]
[12*07]
[12*08]
[12*09]
[12*10]
[12*11]
[12*12]
[12*13]
[12*14]
[12*15]
The values are actually different rows. So [12*01] is row 1 and [12*02] is row # 2 and so on. I want to use these values as column headers. So [12*01] is column 1 and [12*02] is column 2 and so on. I would have used a pivot but this string will keep changing every time and thats the very reason I dont want to use pivots. This is not a college homework assignment, and I have only thought of using RANK and row number functions.
Any help would be appreciated.
You will still have to use pivot (or aggregate_function (CASE ) hand-coded variant), but you can do it dynamically. Create a procedure accepting a comma-separated list of column headers and prepare sql code for pivot in varchar variable placing a placeholders where a list should be. This allows you to save pivot code in a table, or read a view definition into varchar variable to help you with syntax checking. After that replace placeholders with actual list and execute pivot.
create proc ExecPivot (@PivotedList nvarchar(max))
as
set nocount on
declare @sql nvarchar(max)
set @sql = N'
select ColumnList, [PLACEHOLDER]
from TableA
pivot
(
min(Value)
for Key in ([PLACEHOLDER])
) a'
set @sql = REPLACE(@sql, '[PLACEHOLDER]', @PivotedList)
print @sql
exec sp_executesql @sql
There is a good reference for concatenating many rows into a single text string. You will probably need it for @PivotedList parameter.
I hope I am not entirely of the mark :-)