Pivoting SQL Server 2005 for unknown number of rows

Question

My table is a dynamic one. E.g.:

id      SUBJECT
1   his
2   math
3   sci
4   opt
5   ENG
6   SOC

The number of rows is not limited. There could be a hundred. I want output like this:

ID 1     2     3      4     5      6  

   HIS  MATH    SCI   OPT   ENG   SOC

I could use a pivot query, but I would have to know the number of columns. How can I do this without knowing the number of columns in advance?

Answer 1

Dynamic SQL is an option.

Answer 2

i got an answer pals.... but its very tricky see this

create a table for all ur records

count the records

create a table with that much number of columns

create a comma seperated variable for the table which has records

then split the comma sep variables into multuple columns

here is the code

DECLARE @HEADDESC NVARCHAR(150) DROP TABLE #HEADS CREATE TABLE #HEADS ( ID INT IDENTITY ,HEADS NVARCHAR(150) ,NU INT ) DECLARE @NO INT; SET @NO = 0 DECLARE C1 CURSOR FOR (SELECT HEADDESC FROM GMC.FEEHEAD_MASTER WHERE CODE = 'GF' AND HEADDESC<>'') OPEN C1 FETCH NEXT FROM C1 INTO @HEADDESC WHILE @@FETCH_STATUS = 0 BEGIN PRINT @HEADDESC SET @NO = @NO+1 INSERT INTO #HEADS(HEADS,NU) VALUES(@HEADDESC,@NO)

FETCH NEXT FROM C1 INTO @HEADDESC END --SELECT * FROM #HEADS CLOSE C1 DEALLOCATE C1 DECLARE @COLNO INT

SET @COLNO = (SELECT COUNT(*) FROM #HEADS)

DECLARE @COLUMNS VARCHAR(8000)

SELECT @COLUMNS = COALESCE(@COLUMNS +','+ CAST(HEADS AS VARCHAR) , CAST(HEADS AS VARCHAR)) FROM #HEADS --GROUP BY HEADS

declare @value nvarchar(100)

set @value = ',1,STUDENTIDNO,STUDENTNAME,'

SET @COLUMNS = @VALUE+@COLUMNS SET @COLNO = @COLNO+4 --SELECT @COLUMNS DROP TABLE #HEADSCOMMA CREATE TABLE #HEADSCOMMA(HEADS NVARCHAR(3000))

INSERT INTO #HEADSCOMMA VALUES (@COLUMNS)

DROP TABLE #TEMP CREATE TABLE #TEMP(COL1 NVARCHAR(1000)) DECLARE @SQL NVARCHAR(MAX) DECLARE @COL NVARCHAR(1000) DECLARE @COL1 INT DECLARE @COLNAME NVARCHAR(1000)

SET @COL1 = 2 SET @COL = 'COL' PRINT @COL1 --SET @COLNAME = @COL +CAST(@COL1 AS NVARCHAR(10)) WHILE @COL1 < =@COLNO BEGIN SET @COLNAME = @COL +CAST(@COL1 AS NVARCHAR(100)) PRINT @COLNAME SET @SQL = 'ALTER TABLE #TEMP ADD '+@COLNAME+' NVARCHAR(100)' EXEC(@SQL) SET @COL1= @COL1+1 END

--SELECT * FROM #HEADSCOMMA -- COMMA SEPERATED VALUES DECLARE @S VARCHAR(8000), @DATA VARCHAR(8000) --DROP TABLE #NORMALISEDTABLE --CREATE TABLE #NORMALISEDTABLE (HEADS NVARCHAR(200)) SELECT @S='' WHILE EXISTS (SELECT * FROM #HEADSCOMMA WHERE HEADS>@S) BEGIN SELECT @S=HEADS FROM #HEADSCOMMA WHERE HEADS>@S PRINT @S SELECT @DATA=''''+REPLACE(@S,',',''',''')+'''' PRINT @DATA INSERT INTO #TEMP EXEC('SELECT '+@DATA) END

select * from #temp

will give the records