I you have a table F1,
And table 2 like Child. Then the pivot query will be,
- declare@ pp varchar(max)
- declare@ pp1 varchar(max)
- declare@ pivot varchar(max)
- create table pivot_columns(pivot_column varchar(100))
- set@ pp = ' insert into pivot_columns select distinct Cid from child'
- exec(@pp)
- --set@ sql1 = 'select pivot_column from pivot_columns'
- select@ pivot = coalesce(@pivot + ',', '') + '[' + pivot_column + ']'
- from pivot_columns
- set@ pp1 = 'Select * from ( Select Pid,Cid,Name from Child) as SourceTable Pivot (max(name) for CId in (' + @pivot + ')) as PivotTable'
- drop table pivot_columns
- print(@pp1)