Pivoting SQL Server 2005 for unknown number of rows

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?

i got an answer but it's very tricky

1. create a table for all your records
2. count the records
3. create a table with that much number of columns
4. create a comma separated variable for the table which has records
5. then split the comma separated variables into multiple 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

Dynamic SQL is an option.