select ygbh,substring(zw,n.i,1) as zw
from ygb
,
(
select 1 as i
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
) N
where substring(zw,n.i,1) <> ''
and substring(zw,n.i,1) <> ','
order by ygbh
from ygb
,
(
select 1 as i
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
) N
where substring(zw,n.i,1) <> ''
and substring(zw,n.i,1) <> ','
order by ygbh
解决方案 »
- 字符串作列名
- 获取用户最新登录时间
- 紧急求助 Update 错了 字段 错误更新了100万数据 救民阿 救民阿 救民阿 救民阿 救民阿
- 请教:如何使用变量(1)变量:@a1=100,@a2=200,...(2)变量:@b等于'@a1'或'@a2'或、、、(3)求变量:@c等于@b所表示的@a1或@a2或、、、之值
- 这段分页程序错在哪儿?
- 看看我这样调用存储过程是否有问题,急急急,我需要各位的帮助,谢谢!
- 动态sql语句
- 请问怎么样保存单位中的平方??如平方米:M2??
- 请问数据库的压缩备份怎样做好,我的数据量很大,每个月有几G。(SQLSERVER)
- 怎样用SQL语句把一个数据库中符合条件的记录作修改.如:
- 关于MSSQL服务管理器的启动问题(在线等待)???!!!
- 列转为行
select top 8000 id=identity(int,1,1) into #t from syscolumns a,syscolumns b--进行处理
select ygbh,zw=substring(zw,id,charindex(',',zw+',',id)-id)
from ygb a join #t b on substring(','+zw,id,1)=','
create table ygb(ygbh int,zw varchar(8000))
insert ygb select 1,'1,3'
union all select 2,'1'
union all select 3,'2,3,5'
go--创建一个处理的临时表
select top 8000 id=identity(int,1,1) into #t from syscolumns a,syscolumns b--进行处理
select ygbh,zw=substring(zw,id,charindex(',',zw+',',id)-id)
from ygb a join #t b on substring(','+zw,id,1)=','
go--删除测试
drop table ygb,#t/*--测试结果ygbh zw
----------- ----------
1 1
1 3
2 1
3 2
3 3
3 5(所影响的行数为 6 行)--*/
as
select ygbh
,substring(zw,n.i,charindex(',',zw+',',n.i)-n.i) as zw
from ygb
,
(
select 1 as i
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
) Nwhere substring(','+zw,n.i,1) = ','
1 + n.i + m.i * 10
,substring(zw,1 + n.i + m.i * 10 ,case when charindex(',',zw+',',1 + n.i + m.i * 10 ) >= (1 + m.i + n.i * 10) then charindex(',',zw+',',1 + n.i + m.i * 10 ) - (1 + m.i + n.i * 10) else (1 + m.i + n.i * 10) end
) as zw
from
ygb
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9) Nwhere substring(','+zw,1 + n.i + m.i * 10,1) = ','