两个 sql

[振乾]

--set identity_insert test.[dbo].Student ON
--问题一 简单
IF (OBJECT_ID('Student') IS NOT NULL)
  DROP TABLE Student
   CREATE TABLE Student
   (
       ID INT IDENTITY(1,1) PRIMARY KEY,
       NAME VARCHAR(50),
       Infos VARCHAR(500)
   )
INSERT  Student
SELECT 'zhangsan','80;88;100' UNION ALL 
SELECT 'lisi','83;78;90'  
SELECT * FROM Student s
--结果
   ID  NAME       col1  col2 col3
   1   zhangsan    80    88   100
   …………



--问题二 稍微复杂些
IF (OBJECT_ID('Student') IS NOT NULL)
  DROP TABLE Student
   CREATE TABLE Student
   (
       ID INT IDENTITY(1,1) PRIMARY KEY,
       NAME VARCHAR(50),
       Infos VARCHAR(500)
   )
INSERT  Student
SELECT 'zhangsan','语文:80;数学:88;英语:100' UNION ALL 
SELECT 'lisi','语文:83;数学:78;英语:90'  
--结果
   ID  NAME       语文  数学 英语
   1   zhangsan    80    88   100
   …………
 

请各位赐教。

[百年树人]

参考

/**
 --功能：分割字符串，取第@i个值
 --Author:josy(百年树人)
 --参数@s:字符串
 --参数@i:取第几个值
 --参数@sign:分隔符
**/
create function [dbo].[f_col](@s varchar(100),@i int,@sign varchar(10))
returns varchar(20)
as
begin
  declare @t table(id int identity(1,1),col varchar(10));
  declare @cnt int,@rel varchar(20)
  set @s=@s+@sign
  set @cnt=datalength(@s)-datalength(replace(@s,@sign,''))
  while @cnt>0
  begin
    insert @t(col) select left(@s,charindex(@sign,@s)-1)
    set @cnt=@cnt-1
    set @s=stuff(@s,1,charindex(@sign,@s),'')
  end
  select @rel=col from @t where id=@i
  return @rel
end
go

根据这个函数查询的结果，再做简单的处理即可

[百年树人]

select 
  name,
  replace(dbo.f_col(Infos,1,';'),'语文:','') as 语文, 
  replace(dbo.f_col(Infos,2,';'),'数学:','') as 数学, 
  replace(dbo.f_col(Infos,3,';'),'英语:','') as 英语
from Student

/**
zhangsan	80	88	100
lisi	83	78	90
**/

[jaydom]

--问题一 

select a.ID,a.NAME,col1=left(Infos,CHARINDEX(';',Infos)-1),
       col2=left(stuff(Infos,1,charindex(';',Infos),''),CHARINDEX(';',stuff(Infos,1,charindex(';',Infos),''))-1),
       col3=reverse(left(reverse(Infos),CHARINDEX(';',reverse(Infos))-1))
from Student a 

ID	NAME	col1	col2	col3
1	zhangsan	80	88	100
2	lisi	83	78	90





--问题二

select ID,NAME,语文= max(case when LEFT(Infos,CHARINDEX(':',Infos)-1)='语文' then RIGHT(Infos,LEN(Infos)-CHARINDEX(':',Infos)) else null end),
               数学= max(case when LEFT(Infos,CHARINDEX(':',Infos)-1)='数学' then RIGHT(Infos,LEN(Infos)-CHARINDEX(':',Infos)) else null end),
               英语= max(case when LEFT(Infos,CHARINDEX(':',Infos)-1)='英语' then RIGHT(Infos,LEN(Infos)-CHARINDEX(':',Infos)) else null end)
from
(
select ID,a.NAME,Infos=SUBSTRING(a.Infos,number,CHARINDEX(';',a.Infos+';',number)-number)
from Student a join master..spt_values b on b.type='p'
     and CHARINDEX(';',';'+Infos,number)=number
) a
group by ID,NAME     
     
 
 ID	NAME	语文	数学	英语
1	zhangsan	80	88	100
2	lisi	83	78	90