要求三列字段值都不固定 表名:st_moni 次 成品型号 总价 1 a 25 2 b 26 1 a 30 2 b 35想把它转成:成品型号 1 2 a 25 26 b 30 35在ACCESS中可以这样用: TRANSFORM Sum(st_moni.总价) AS 总价之总计 SELECT st_moni.成品型号 FROM st_moni GROUP BY st_moni.成品型号 PIVOT st_moni.次;请问在SQL2000中该怎么写?
完全是为了抢分~~~ ============================ 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] GOINSERT 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
--用于:交叉表的列数是不确定的 declare @sql varchar(8000)set @sql = 'select name,' select @sql = @sql + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''',' from (select distinct subject from test) as a select @sql = left(@sql,len(@sql)-1) + ' from test group by name' exec(@sql) go
create table hzl (name char(10),km char(10),cj int) go insert hzl values('张三','语文',80) insert hzl values('张三','数学',86) insert hzl values('张三','英语',75) insert hzl values('李四','语文',78) insert hzl values('李四','数学',85) insert hzl values('李四','英语',78) select * from hzl declare @sql varchar(8000) set @sql = 'select name' select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']' from (select distinct km from hzl) as a select @sql = @sql+' from hzl group by name' exec(@sql)drop table hzl
CREATE TABLE T(次 INT,成品型号 VARCHAR(10),总价 INT) INSERT INTO T SELECT 1,'a',25 UNION ALL SELECT 2,'b',26 UNION ALL SELECT 1,'a',30 UNION ALL SELECT 2,'b',35DECLARE @S VARCHAR(8000) SET @S='SELECT 成品型号' SELECT @S=@S+',['+LTRIM(次)+']=SUM(CASE WHEN 次='''+LTRIM(次)+''' THEN 总价 ELSE 0 END)' FROM T GROUP BY 次,成品型号 SET @S=@S+' FROM T GROUP BY 次,成品型号' EXEC(@S)DROP TABLE T/*成品型号 1 2 ---------- ----------- ----------- a 55 0 b 0 61 */
不好意思,写错了,数据确实有问题,应改为: 表名:st_moni 次 成品型号 总价 1 a 25 2 a 26 1 b 30 2 b 35
CREATE TABLE T(次 INT,成品型号 VARCHAR(10),总价 INT) INSERT INTO T SELECT 1,'a',25 UNION ALL SELECT 2,'a',26 UNION ALL SELECT 1,'b',30 UNION ALL SELECT 2,'b',35DECLARE @S VARCHAR(8000) SET @S='SELECT 成品型号' SELECT @S=@S+',['+LTRIM(次)+']=SUM(CASE WHEN 次='''+LTRIM(次)+''' THEN 总价 ELSE 0 END)' FROM T GROUP BY 次 SET @S=@S+' FROM T GROUP BY 成品型号' EXEC(@S)DROP TABLE T/* 成品型号 1 2 ---------- ----------- ----------- a 25 26 b 30 35 */
============================
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]
GOINSERT 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
--用于:交叉表的列数是不确定的
declare @sql varchar(8000)set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go
go
insert hzl values('张三','语文',80)
insert hzl values('张三','数学',86)
insert hzl values('张三','英语',75)
insert hzl values('李四','语文',78)
insert hzl values('李四','数学',85)
insert hzl values('李四','英语',78)
select * from hzl
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from hzl) as a
select @sql = @sql+' from hzl group by name'
exec(@sql)drop table hzl
INSERT INTO T SELECT 1,'a',25
UNION ALL SELECT 2,'b',26
UNION ALL SELECT 1,'a',30
UNION ALL SELECT 2,'b',35DECLARE @S VARCHAR(8000)
SET @S='SELECT 成品型号'
SELECT @S=@S+',['+LTRIM(次)+']=SUM(CASE WHEN 次='''+LTRIM(次)+''' THEN 总价 ELSE 0 END)' FROM T
GROUP BY 次,成品型号
SET @S=@S+' FROM T GROUP BY 次,成品型号'
EXEC(@S)DROP TABLE T/*成品型号 1 2
---------- ----------- -----------
a 55 0
b 0 61
*/
表名:st_moni
次 成品型号 总价
1 a 25
2 a 26
1 b 30
2 b 35
INSERT INTO T SELECT 1,'a',25
UNION ALL SELECT 2,'a',26
UNION ALL SELECT 1,'b',30
UNION ALL SELECT 2,'b',35DECLARE @S VARCHAR(8000)
SET @S='SELECT 成品型号'
SELECT @S=@S+',['+LTRIM(次)+']=SUM(CASE WHEN 次='''+LTRIM(次)+''' THEN 总价 ELSE 0 END)' FROM T
GROUP BY 次
SET @S=@S+' FROM T GROUP BY 成品型号'
EXEC(@S)DROP TABLE T/*
成品型号 1 2
---------- ----------- -----------
a 25 26
b 30 35
*/