数据库行转列
‘壹’ 请帮忙把下面数据库表行变列吧
select t.字段1,
sum(decode(t.字段3, '2', 字段4,null)) as 字段3,
sum(decode((t.字段3, 600', 字段4,null)) as 字段3,
sum(decode(t.字段3, '50', 字段4,null)) as 字段3 ，
sum(decode(t.字段3, '100', 字段4,null)) as 字段3
from 表名字 t
group by t.字段1
order by t.字段1
‘贰’ sql中行变成列
这是一个行列转换的问题嘛，可以用case when then来做
select sno,
sum(case course when '语文' then source else 0 end) as 语文,
sum(case course when '数学'then source else 0 end) as 数学
from 表名 group by sno
关于行列转换的详细介绍你可以在网上找找，这个问题个人感觉比较重要，建议多看看
‘叁’ sql语句行转列 怎么转啊
--声明变量
declare@sqlvarchar(1000),@num_dataint,@num_allvarchar(2000),@num_numint,@table_sqlvarchar(2000)
set@num_num=0
--判断并创建表
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[records]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[dbo].[records]
createtablerecords(
[id]int,
[name]varchar(50),
[sex]varchar(10),
[num]int
)
--插入数据
insertintorecordsvalues(1,'tom','男',2)
insertintorecordsvalues(1,'tom','男',3)
insertintorecordsvalues(1,'tom','男',4)
insertintorecordsvalues(1,'tom','男',5)
--全选表中数据
select*fromrecords
--全选num列数据
selectnumas'数据'fromrecords
--释放游标
deallocateselect_num
--为‘selectnumfromrecords’建立游标
declareselect_numscrollcursorforselectnumas'shuju'fromrecords
--打开游标
openselect_num
--获得第一条数据
fetchnextfromselect_numinto@num_data
set@num_all=convert(varchar,@num_data)+','
set@num_num=@num_num+1;
--如果获取成功，继续获得数据
while@@fetch_status=0
begin
fetchnextfromselect_numinto@num_data
set@num_num=@num_num+1;
set@num_all=@num_all+convert(varchar,@num_data)+','
end
--关闭游标
closeselect_num
print@num_num
--set@num_num=@num_num-1;
declare@iint
set@i=1
print@num_num
print@i
set@table_sql='createtablenumall(idint,namevarchar(50),sexvarchar(10)'
print@table_sql
while@num_num>=1
begin
set@table_sql=@table_sql+',num'+convert(varchar,@i)+'int'
set@num_num=@num_num-1;
set@i=@i+1
end
set@table_sql=@table_sql+')'
print@table_sql
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[numall]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[dbo].[numall]
exec(@table_sql)
declare@insert_sqlvarchar(2000)
set@insert_sql='insertintonumallvalues(1,'+'''tom'','+'''男'''
print@insert_sql
openselect_num
--获得第一条数据
fetchnextfromselect_numinto@num_data
set@insert_sql=@insert_sql+','+convert(varchar,@num_data)
--如果获取成功，继续获得数据
while@@fetch_status=0
begin
fetchnextfromselect_numinto@num_data
set@insert_sql=@insert_sql+','+convert(varchar,@num_data)
end
set@insert_sql=@insert_sql+')'
print@insert_sql
exec(@insert_sql)
--insertintonumallvalues(1,'tom','男',2,3,4,5,5)
select*fromnumall
试试吧，数据虽然有点出入，但已经说明问题了！！！
‘肆’ sql server 行转列
创建测试表
createtabletest
(_keyvarchar(10),
_valuevarchar(10),
idint)
insertintotestvalues('ceshi','测试值',10)
insertintotestvalues('ceshi','测试值',11)
insertintotestvalues('ceshi2','测试值2',11)
执行
declare@sqlvarchar(4000)
set@sql='selectid'
select@sql=@sql+',max(case_keywhen'''+[_key]+'''then[_value]end)as
['+_key+']'
from(selectdistinct_keyfromtest)asa
select@sql=@sql+'fromtestgroupbyid'
exec(@sql)
结果
额，我那个第三条数据id写错了，不过方法还是这个
‘伍’ sql行转列
SELECT e.NAME as name,
count(case when DATEPART(year,c.START_TIME)=2014 then 1 else null end ) as count2014,
count(case when DATEPART(year,c.START_TIME)=2015 then 1 else null end ) as count2015
from ENVI_DATA_STATISTIC_COMP c
LEFT JOIN ENVI_CITY e on e.CITY_ID=c.CITY_ID
where c.COMP_NAME like '%蓝天白云%' and INDEX_TYPE='12'
and (c.START_TIME like '%2014-08%' or c.START_TIME like '%2015-08%' ) and STATISTIC_TYPE='1' and c.VALID_FLAG='1'
group by name
应该是酱紫的，使用case when 转换~
‘陆’ 怎样实现sql行转列
--试一试。。。
createtable#test
(
姓名nvarchar(10),
学号nvarchar(10),
题号nvarchar(10),
答案nvarchar(10)
)
go
insertinto#test
select'张三','001','1','A'unionall
select'张三','001','2','C'unionall
select'李四','002','1','B'unionall
select'李四','002','2','D'
select*from#test
dECLARE@sqlVARCHAR(max)
SET@sql=''
SELECT@sql=@sql+',['+题号+']'FROM#testGROUPBY题号
SET@sql=STUFF(@sql,1,1,'')
SET@sql='select*from#testpivot(max(答案)for题号in('+@sql+'))a'
PRINT@sql
exec(@sql)
‘柒’ 数据库SQL实现行转列。请懂的人帮忙，谢谢！
没有环境,试试下面语句可否实现(oracle):
select Customer.CustomerId,
Customer.CustomerName
wm.concat(Phone.PhoneNumber)
from Customer left join
(
select CustomerPhone.CustomerId,
Phone.PhoneNumber
from CustomerPhone inner join Phone on CustomerPhone.PhoneId=Phone.PhoneId
order by CustomerPhone.CustomerId
)Phone on Customer.CustomerId=Phone.CustomerId
‘捌’ sql 行转列
最简答的方法：使用程序数组，例如你现在的代码是：
do while not rs.eof
response.write rs("....")
rs.movenext
end do
那么可以下面这样输出：
'先为每个字段定义数组
dim a(1)
dim b(1)
n=1
do while not rs.eof
a(n)=rs("a")
b(n)=rs("b")
'...有多少字段写多少行....
n=n+1
rs.movenext
end do
'下面再输出
response.write "<table>";
response.write "<tr><td>" & join("<td>",a)
response.write "<tr><td>" & join("<td>",b)
response.write "</table>";
你的ASP程序不可能显示多少条，一般每页显示20条左右，用数组在显示的转换是可行的。
‘玖’ SQL 行转列
我这里两种都给你介绍，行转列和列转行：
列转行——
录入经营范围时候会遇到列传行的问题解决方案如下：
在temp1 表有一下字段内容：