想对数据库表PCode的字段PCID进行整理!表PCode:PCID
P11234
P11212
P11324
P12334
P12445
P12556
P23445
P23555想对字段PCID
前3个字符为P11所有值加入到临时表A的字段a1里
前3个字符为P12所有值加入到临时表A的字段a2里
前3个字符为P23所有值加入到临时表A的字段a3里
前3个字符为P24所有值加入到临时表A的字段a4里表A的最终结果如下所示:
A:
a1 a2 a3 a4
P11234 P12334 P23445
P11212 P12445 P23555
P11324 P12556请问要怎么做会比较好些?谢谢!
P11234
P11212
P11324
P12334
P12445
P12556
P23445
P23555想对字段PCID
前3个字符为P11所有值加入到临时表A的字段a1里
前3个字符为P12所有值加入到临时表A的字段a2里
前3个字符为P23所有值加入到临时表A的字段a3里
前3个字符为P24所有值加入到临时表A的字段a4里表A的最终结果如下所示:
A:
a1 a2 a3 a4
P11234 P12334 P23445
P11212 P12445 P23555
P11324 P12556请问要怎么做会比较好些?谢谢!
解决方案 »
- 如何把数据表中某一行的多个列的值用Teechart图表饼图表示出来呢?
- OleContainer+Excel问题:EXCEL如何向DELPHI程序发消息?
- 如何播放流媒体?
- 帮我翻译一下
- 求救,如何将结构相同的Excel表数据导入到Access数据库中?谢谢
- Tclientsocket令系统出错
- 怎样注册ACCESS的ODBC?
- 报表打印设置问题,急急急!!!100分给你!!!
- 如何实现非报表打印输出,例如打印小票单据,请问用什么语句可以直接输出到打印机??
- 关于imagelist的应用,太菜了,都问不出口了。呵呵
- 跪求 在Dll文件里如何编写Sql操作语句
- combobox,怎么把它默认的右键菜单去掉
create table grade(id int identity(1,1) primary key, name varchar(10) , subject varchar(50) ,grade decimal)
create table subject(subject varchar(50) primary key)insert into subject values('math')
insert into subject values('Eng.')
insert into subject values('Chi.')
insert into subject values('chem.')insert into grade values('yuan', 'math', 100)
insert into grade values('yuan', 'Eng.', 70)
insert into grade values('yuan', 'Chi.', 80)
insert into grade values('yuan', 'chem.', 90)--存储过程脚本内容
declare @Sql nvarchar(4000)set @Sql = 'select name 'select @Sql = @Sql + ', sum(case subject when ''' + subject + ''' then grade else 0 end) as [' + subject + ']'
from subject
order by subjectprint @Sql set @Sql = @Sql + ' into ##result from grade group by name'print @Sql exec(@Sql)select * from ##result
就行了
select case when substr(pcid,0,3) = p11 then pcid end a_a1,
case when substr(pcid,0,3) = p12 then pcid end a_a2,
case when substr(pcid,0,3) = p23 then pcid end a_a3,
case when substr(pcid,0,3) = p24 then pcid end a_a4
from PCode
select (case when PCID=p1% then PCID else null end) as a1,
(case when PCID=p2% then PCID else null end) as a2,
(case when PCID=p3% then PCID else null end) as a3,
(case when PCID=p4% then PCID else null end) as a4,
.............
from Table
呵呵,可能写的麻烦了,权当参考:)
#temptable 中的数据就是你想要要的,再将#temptable中的数据导入到你想插入的表中就可以了
试试吧!/*
create table #temptable
(
ident int,
a1 varchar(50),
a2 varchar(50),
a3 varchar(50),
a4 varchar(50)
)*/
declare @t1 integer
declare @t2 integer
declare @t3 integer
declare @t4 integerset @t1 = 1
set @t2 = 1
set @t3 = 1
set @t4 = 1declare @str varchar(50)
declare @colname varchar(50)
declare A_Cursor cursor for select PCid from PCode
open A_Cursor
while ( 1 > 0)
begin
fetch next from A_Cursor into @str
if ( @@FETCH_STATUS <> 0 ) break
--插入操作
if (substring(@str,1,3) = 'p11')
begin
print('@1')
if exists( select * from #temptable where ident = @t1 )
begin
update #temptable
set a1 = @str
where ident = @t1
end
else
begin
insert into #temptable (ident,a1) values (@t1,@str)
end
set @t1 = @t1+1
end
if (substring(@str,1,3) = 'p12')
begin
print('@2')
if exists( select * from #temptable where ident = @t2 )
begin
update #temptable
set a2 = @str
where ident = @t2
end
else
begin
insert into #temptable (ident,a2) values (@t2,@str)
end
set @t2 = @t2+1
end
if (substring(@str,1,3) = 'p23')
begin
print('@3')
if exists( select * from #temptable where ident = @t3 )
begin
update #temptable
set a3 = @str
where ident = @t3
end
else
begin
insert into #temptable (ident,a3) values (@t3,@str)
end
set @t3 = @t3+1
end
if (substring(@str,1,3) = 'p24')
begin
print('@4')
if exists( select * from #temptable where ident = @t4 )
begin
update #temptable
set a4 = @str
where ident = @t4
end
else
begin
insert into #temptable (ident,a4) values (@t4,@str)
end
set @t4 = @t4+1
end
--插入操作结束
print(@str)
end
close A_Cursor
deallocate A_Cursorselect * from #temptable
--drop table #temptable