（在线等待）sql怎样将列换成行(30分)

bijiaofan · 2008-11-05

a                    b          c            d 2070453302435 1 0 2008-08-30 08:20:37.943 2070453302435 2 0 2008-09-20 08:32:24.783 2071202700399 1 0 2008-08-30 08:50:41.737 2071202700399 2 0 2008-09-20 08:30:10.757 我想换成 a               b1 b2 c      d1                        d2 2070453302435   1  2  0   2008-08-30 08:20:37.943   2008-09-20 08:32:24.783 2071202700399   1  2  0   2008-08-30 08:50:41.737   2008-09-20 08:30:10.757 谢谢！

jiaa · 2008-11-05

最好用临时表，也可以用这个嵌套查询（哈哈比较懒只写了b1，b2） select a,sum(b1),sum(b2) from ( select a,case when b=1 then b else 0 end as b1,case when b=2 then b else 0 end as b2 from xxxxxx ) group by a

007vivi · 2008-11-05

sql列转行2007-01-15 10:12 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 --用于：交叉表的列数是不确定的 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 一个列转行的SQL（竖表变成横表） 2008-03-19 19:20 CREATE TABLE [dbo].[student]( [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [kecheng] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [chengji] [int] NULL ) ON [PRIMARY] 表结构： name         kecheng           chengji     张三              语文                  78 李斯             语文                    89       张三              数学                  78 李斯              数学                    89     ----------------------------------------------------------------------------------------------------- declare cur cursor for select distinct kecheng from student declare @kechengforcur varchar(50) declare @strsql varchar(2000) set @strsql='select name ' open cur fetch from cur into @kechengforcur while @@fetch_status=0 begin    set @strsql=@strsql+', sum(case kecheng when '''+@kechengforcur+''' then chengji end)as '+ @kechengforcur --   set @strsql=@strsql+', sum(case kecheng when '''+@kechengforcur+''' then chengji else 0 end)as '+ @kechengforcur    fetch from cur into @kechengforcur end set @strsql=@strsql+', round(avg(chengji+0.0),2)as 平均分,sum(chengji)as 总分' set @strsql=@strsql+' from student group by name' close cur DEALLOCATE cur print @strsql exec(@strsql) 结果： name               语文                      数学          平均分          总分 张三                 78                            78            78                   156 李斯                  89                         89             89                  178   sql server行转列 - 列转行--交叉表实现 2007/11/21 19:38 主要应用case语句来解决行转列的问题 行转列问题主要分为两类 1)简单的行转列问题: 示例表: id sid           course result 1   2005001 语文     80.0 2   2005001 数学     90.0 3   2005001 英语     80.0 4   2005002 语文     56.0 5   2005002 数学     69.0 6   2005002 英语     89.0 执行 select sid,语文=isnull(sum(case course when '语文' then result end),0),    数学=isnull(sum(case course when '数学' then result end),0),    英语=isnull(sum(case course when '英语' then result end),0)    from result    group by sid    order by sid 得出结果 sid           语文数学英语 2005001 80.0 90.0 80.0 2005002 56.0 69.0 89.0 2)较为复杂的行转列 表1:course id name 1 语文 2 数学 3 英语 表2:result id sid          course result 1 2005001 语文      80.0 2 2005001 数学      90.0 3 2005001 英语      80.0 4 2005002 语文      56.0 5 2005002 数学      69.0 6 2005002 英语      89.0 declare @sql varchar(8000) set @sql='select sid' select @sql=@sql+','+course.name+'=isnull(sum(case course when '''+course.name+''' then result end),0)' from course order by id set @sql=@sql+' from result group by sid order by sid' print @sql exec(@sql) 得出结果 sid           语文数学英语 2005001 80.0 90.0 80.0 2005002 56.0 69.0 89.0 -----------------列转行--------------------------------------------------------------------------------------------------------- 有字符串'1,2,3,4,5,,6,7,8,'这样的不定长字符串,要求将其转成一列N行来存储,也就是列转行,哪种方式最快. 经过讨论,下列方式最快. declare @var varchar(8000)     ,@sql varchar(8000)     ,@last varchar(1)     ,@ctrl int set @var='1,2,3,4,5,,6,7,' set @ctrl=0 while @ctrl=0 begin if (select charindex(',,',@var))>0     select @var=replace(@var,',,',',') else     set @ctrl=1 end set @last=right(@var,1) if @last=',' set @var=left(@var,len(@var)-1) set @sql='select '+replace(@var,',',' union all select ' -------------------------------------------------可运行的------------------------------------ --交叉表语句的实现： CREATE TABLE Test ( id int IDENTITY(1,1) NOT NULL, name nvarchar(50) NULL, subject nvarchar(50) NULL, Source numeric(18,0) NULL ) 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 * FROM TEST --用于：交叉表的列数是确定的 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 = @sql + ' from test group by name' exec(@sql)

（在线等待）sql怎样将列换成行(30分)

bijiaofan

Unregistered / Unconfirmed

jiaa

Unregistered / Unconfirmed

007vivi

Unregistered / Unconfirmed

Similar threads