create table table1(zsid int,name varchar(50)) insert into table1 select 1 ,'a' insert into table1 select 1 ,'b' insert into table1 select 1 ,'c' insert into table1 select 1 ,'d' insert into table1 select 1 ,'e' insert into table1 select 1 ,'f'declare @sql varchar(8000) select @sql=isnull(@sql+',','')+'max(case when name='''+name+''' then name end) as ['+name+']' from table1 exec('select zsid,'+@sql+' from table1 group by zsid')zsid a b c d e f 1 a b c d e f
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 (N'张三',N'语文',60) INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70) INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75) INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57) INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100) Go--交叉表语句的实现: --用于:交叉表的列数是确定的 select name,sum(case subject when '数学' then source else 0 end) as '数学', sum(case subject when '英语' then source else 0 end) as '英语', sum(case subject when '语文' then source else 0 end) as '语文' from test group by name
谢谢你的解答. 还有一点问题 我的数据不值这么多 ..insert into table1 select 1 ,'a' insert into table1 select 1 ,'b' insert into table1 select 1 ,'c' insert into table1 select 1 ,'d' insert into table1 select 1 ,'e' insert into table1 select 1 ,'f' 这些也应该 动态的
sql server2005中有个pivot函数 IVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式例子 USE AdventureWorks; GO SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) ) AS pvt ORDER BY VendorID
insert into table1 select 1 ,'a'
insert into table1 select 1 ,'b'
insert into table1 select 1 ,'c'
insert into table1 select 1 ,'d'
insert into table1 select 1 ,'e'
insert into table1 select 1 ,'f'declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when name='''+name+''' then name end) as ['+name+']'
from table1
exec('select zsid,'+@sql+' from table1 group by zsid')zsid a b c d e f
1 a b c d e f
[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 (N'张三',N'语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
Go--交叉表语句的实现:
--用于:交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name
还有一点问题
我的数据不值这么多 ..insert into table1 select 1 ,'a'
insert into table1 select 1 ,'b'
insert into table1 select 1 ,'c'
insert into table1 select 1 ,'d'
insert into table1 select 1 ,'e'
insert into table1 select 1 ,'f'
这些也应该 动态的
IVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式例子
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID