CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
--加入数据:
INSERT INTO [test] ([name],[subject],[Source]) values ('张三','语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('张三','英语',100)
Go
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT name'
SELECT @SQL= @SQL+ ',max(CASE WHEN subject = ''' + subject + ''' THEN Source else 0 END) [' + subject + ']' FROM (SELECT DISTINCT subject FROM Test) A
SET @SQL=@SQL+' FROM Test GROUP BY name'
exec (@SQL)
/*
name 数学 英语 语文
李四 70 0 80
王五 75 80 57
张三 0 100 60
*/
drop table Test
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
--加入数据:
INSERT INTO [test] ([name],[subject],[Source]) values ('张三','语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('张三','英语',100)
Go
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT name'
SELECT @SQL= @SQL+ ',max(CASE WHEN subject = ''' + subject + ''' THEN Source else 0 END) [' + subject + ']' FROM (SELECT DISTINCT subject FROM Test) A
SET @SQL=@SQL+' FROM Test GROUP BY name'
exec (@SQL)
/*
name 数学 英语 语文
李四 70 0 80
王五 75 80 57
张三 0 100 60
*/
drop table Test
insert # exec (@SQL)
select * from #
drop table #,test
可以在 SELECT @SQL= @SQL+ ',max(CASE WHEN subject = ''' + subject + ''' THEN Source else 0 END) [' + subject + ']' into ##表/或实体表 FROM (SELECT DISTINCT subject FROM Test) A
SET @SQL=@SQL+' FROM Test GROUP BY name'
exec (@SQL)
http://community.csdn.net/Expert/topic/5500/5500431.xml?temp=.6245233
这个贴子中我写的例子
对不起:
请问不用存储过程可以么,我想插入的临时表列头确实是不固定的谢谢指点!!!
SET @SQL=@SQL+' into ##t FROM Test GROUP BY name'
EXEC(@SQL)
SELECT * FROM ##t
DROP TABLE ##t,test
fcuandy(了此残生.) 多谢多谢,大师就是大师啊,真不一样。请教
请教,教教我学sql 的秘诀吧。