请教行转列的sql语句

2007-08-04 11:26:38 183 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

数据库行转列的sql语句问题描述假设有张学生成绩表(CJ)如下Name Subject Result张三语文 80张三数学 90张三物理 85李四语文 85李四数学 92李四物理 82现在想写 sql 语句查询后结果为姓名语文数学物理张三 80 90 85李四 85 92 82 该怎么实现？研究意义这是个并不复杂的问题，但却是数阅读详情

数据表：SGMSCB
字段：
SCB001(varchar) SCB002（varchar） SCB003（numeric）
张三 2007-08-05 10:57:04.933 1000
张三 2007-08-05 11:57:04.933 2000
李四 2007-08-06 08:57:04.933 4000
李四 2007-08-07 09:57:04.933 3000
...................

想得到的结果：
SCB001 2007-08-05 2007-08-06 2007-08-07 ....
张三 3000 0（or null） 0（or null）
李四 0（or null）4000 3000

...全文

ETL第八章无人售货机零售项目实践 ETL实战项目——无人售货机阅读详情

firestoneman

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

lang8134 2007-08-04

打赏
举报

create table # (
SCB001 varchar(10),
SCB002 varchar(50),
SCB003 numeric(10,2))

insert #
select '张三', '2007-08-05 10:57:04.933', 1000
union all
select '张三', '2007-08-05 11:57:04.933', 2000
union all
select '李四', '2007-08-06 08:57:04.933', 4000
union all
select '李四', '2007-08-07 09:57:04.933', 3000

declare @sql varchar(2000)

select @sql = 'select #.SCB001, '

select @sql = @sql + 'sum(case convert(varchar(10),#.SCB002) when '''+ t_rq.rq + ''' then SCB003 else 0 end ) as ''' + t_rq.rq +''','

from (select rq = convert(varchar(10),SCB002) from # group by convert(varchar(10),SCB002)) t_rq

select @sql = left(@sql,len(@sql) - 1) + ' from # group by #.SCB001'
print @sql
exec (@sql)

drop table #
---------------------------------------------------------
结果
SCB001 2007-08-05 2007-08-06 2007-08-07
-----------------------------------------------
李四 .00 4000.00 3000.00
张三 3000.00 .00 .00

firestoneman 2007-08-04

打赏
举报

谢谢各位，先给分，再研究一下。

paoluo 2007-08-04

打赏
举报

--創建測試環境
Create Table SGMSCB
(SCB001 varchar(20),
SCB002 varchar(30),
SCB003 numeric(10, 0))
--插入數據
Insert SGMSCB Select N'张三', '2007-08-05 10:57:04.933', 1000
Union All Select N'张三', '2007-08-05 11:57:04.933', 2000
Union All Select N'李四', '2007-08-06 08:57:04.933', 4000
Union All Select N'李四', '2007-08-07 09:57:04.933', 3000
GO
--測試
Declare @S Varchar(8000)
Select @S = ' Select SCB001'
Select @S = @S + ', SUM(Case Left(SCB002, 10) When ''' + SCB002 + ''' Then SCB003 Else 0 End) As [' + SCB002 + ']'
From (Select Distinct Left(SCB002, 10) As SCB002 From SGMSCB) A Order By SCB002
Select @S = @S + ' From SGMSCB Group By SCB001'
EXEC(@S)
GO
--刪除測試環境
Drop Table SGMSCB
--結果
/*
SCB001 2007-08-05 2007-08-06 2007-08-07
张三 3000 0 0
李四 0 4000 3000
*/

xmlquit 2007-08-04

打赏
举报

declare @sql varchar(8000)
set @sql = 'select SCB001'
select @sql = @sql + ',sum(case substring(SCB002,1,10) as SCB002 when '''+ substring(SCB002,1,10) +''' then SCB003j end) ['+ substring(SCB002,1,10) +']'
from (select distinct substring(SCB002,1,10) as SCB002 from SGMSCB) as a
select @sql = @sql+' from SGMSCB group by SCB001'
exec(@sql)

paoluo 2007-08-04

打赏
举报

--如果SCB002是varchar
Declare @S Varchar(8000)
Select @S = ' Select SCB001'
Select @S = @S + ', SUM(Case Left(SCB002, 10) When ''' + SCB002 + ''' Then SCB003 Else 0 End) As [' + SCB002 + ']'
From (Select Distinct Left(SCB002, 10) As SCB002 From SGMSCB) A Order By SCB002
Select @S = @S + ' From SGMSCB Group By SCB001'
EXEC(@S)

xmlquit 2007-08-04

打赏
举报

declare @sql varchar(8000)
set @sql = 'select SCB001'
select @sql = @sql + ',sum(case substring(SCB002,1,10) when '''+ substring(SCB002,1,10) +''' then cj end) ['+ substring(SCB002,1,10) +']'
from (select distinct substring(SCB002,1,10) from SGMSCB) as a
select @sql = @sql+' from SGMSCB group by SCB001'
exec(@sql)

请教行转列的sql语句

相关推荐