通过临时转换 DECLARE @SQL NVARCHAR(3000) ,@TF_NAME VARCHAR(20) ,@score1 varchar(50) ,@id1 varchar(20) IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME='##TABLE_TEMP') DROP TABLE ##TABLE_TEMP CREATE TABLE ##TABLE_TEMP ( id1 varchar(20) ) DECLARE TEMP_CUR CURSOR FOR SELECT id2name FROM 表 GROUP BY id2name OPEN TEMP_CUR --生成追加字段SQL FETCH NEXT FROM TEMP_CUR INTO @TF_NAME WHILE @@FETCH_STATUS=0 BEGIN SET @SQL=' ALTER TABLE ##TABLE_TEMP ADD '+@TF_NAME+' VARCHAR(50) NULL' EXEC SP_EXECUTESQL @@SQL FETCH NEXT FROM TEMP_CUR INTO @TF_NAME END CLOSE TEMP_CUR DEALLOCATE TEMP_CUR
DECLARE TEMP_CUR CURSOR FOR SELECT id1,score1,id2name FROM 表 OPEN TEMP_CUR --付值 FETCH NEXT FROM TEMP_CUR INTO @id1,@score1,@TF_NAME WHILE @@FETCH_STATUS=0 BEGIN IF EXISTS(SELECT TOP 1 ##TABLE_TEMP FROM id1 = @id1) SET @SQL=' UPDATE ##TABLE_TEMP SET '+ @TF_NAME + ' = '''+@score1+''' WHERE id1='''+@id1+''' ' ELSE SET @SQL=' INSERT ##TABLE_TEMP(id1,'+@TF_NAME+') VALUES('''+@id1+''','''+@score1+''')' EXEC SP_EXECUTESQL @@SQL FETCH NEXT FROM TEMP_CUR INTO @id1,@score1,@TF_NAME END CLOSE TEMP_CUR DEALLOCATE TEMP_CUR SELECT * FROM ##TABLE_TEMP --删除临时表 IF EXISTS (SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME='##TABLE_TEMP') DROP TABLE ##TABLE_TEMP
表
id1 id2 score1 id2name
1 1 50 aaaa
1 2 30 ffff
1 3 70 feee
2 1 20 aaaa
2 5 80 erer
显示成
id1 aaaa ffff feee erer
1 50 30 70
2 20 80
能否帮助你
DECLARE
@SQL NVARCHAR(3000)
,@TF_NAME VARCHAR(20)
,@score1 varchar(50)
,@id1 varchar(20) IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME='##TABLE_TEMP')
DROP TABLE ##TABLE_TEMP CREATE TABLE ##TABLE_TEMP
(
id1 varchar(20)
) DECLARE TEMP_CUR CURSOR FOR
SELECT id2name FROM 表 GROUP BY id2name
OPEN TEMP_CUR
--生成追加字段SQL
FETCH NEXT FROM TEMP_CUR INTO @TF_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL=' ALTER TABLE ##TABLE_TEMP ADD '+@TF_NAME+' VARCHAR(50) NULL'
EXEC SP_EXECUTESQL @@SQL
FETCH NEXT FROM TEMP_CUR INTO @TF_NAME
END
CLOSE TEMP_CUR
DEALLOCATE TEMP_CUR
DECLARE TEMP_CUR CURSOR FOR
SELECT id1,score1,id2name FROM 表
OPEN TEMP_CUR
--付值
FETCH NEXT FROM TEMP_CUR INTO @id1,@score1,@TF_NAME
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT TOP 1 ##TABLE_TEMP FROM id1 = @id1)
SET @SQL=' UPDATE ##TABLE_TEMP SET '+ @TF_NAME + ' = '''+@score1+''' WHERE id1='''+@id1+''' '
ELSE
SET @SQL=' INSERT ##TABLE_TEMP(id1,'+@TF_NAME+') VALUES('''+@id1+''','''+@score1+''')'
EXEC SP_EXECUTESQL @@SQL
FETCH NEXT FROM TEMP_CUR INTO @id1,@score1,@TF_NAME
END
CLOSE TEMP_CUR
DEALLOCATE TEMP_CUR SELECT * FROM ##TABLE_TEMP
--删除临时表 IF EXISTS (SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME='##TABLE_TEMP')
DROP TABLE ##TABLE_TEMP
http://blog.csdn.net/whowhen21***********************************************************[/align]