22,207
社区成员
发帖
与我相关
我的任务
分享
drop table tb
go
create table tb(ID int,date datetime,data varchar(1000))
insert into tb
select 1 ,'2014-01-20 11:36:08.943', '1asd 2assd 3asd 4das 5gfsd' union all
select 2 ,'2014-01-20 11:36:08.943', '6asd 7assd 8asd 9das 10agfsd'
go
declare @sql varchar(8000)
set @sql = ''
;with t
as
(
select id,
date,
SUBSTRING(replace(t.data,' ',','), number ,
CHARINDEX(',',replace(t.data,' ',',')+',',number)-number) as data,
ROW_NUMBER() over(partition by id,date order by @@servername) rownum
from tb t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+replace(t.data,' ',','),s.number,1) = ','
)
select @sql = @sql +
',max(case when rownum = '+CAST(rownum as varchar) +
' then data else null end) as [data'+CAST(rownum as varchar)+']'
from t
group by rownum
select @sql = ';with t
as
(
select id,
date,
SUBSTRING(replace(t.data,'' '','',''), number ,
CHARINDEX('','',replace(t.data,'' '','','')+'','',number)-number) as data,
ROW_NUMBER() over(partition by id,date order by @@servername) rownum
from tb t,master..spt_values s
where s.number >=1
and s.type = ''P''
and SUBSTRING('',''+replace(t.data,'' '','',''),s.number,1) = '',''
)'+
'select id,date'+@sql+
'from t group by id,date'
exec( @sql)
/*
id date data1 data2 data3 data4 data5
1 2014-01-20 11:36:08.943 1asd 2assd 3asd 4das 5gfsd
2 2014-01-20 11:36:08.943 6asd 7assd 8asd 9das 10agfsd
*/
[/quote]
说了,不借助其他手段。拼接字符串也属于其他手段。
如果拼接字符串,直接replace字符为 "as 列名,"就ok,不用你这么麻烦。[/quote]
得动态行转列,但不能用动态语句,这个好像没办法实现的吧。
drop table tb
go
create table tb(ID int,date datetime,data varchar(1000))
insert into tb
select 1 ,'2014-01-20 11:36:08.943', '1asd 2assd 3asd 4das 5gfsd' union all
select 2 ,'2014-01-20 11:36:08.943', '6asd 7assd 8asd 9das 10agfsd'
go
declare @sql varchar(8000)
set @sql = ''
;with t
as
(
select id,
date,
SUBSTRING(replace(t.data,' ',','), number ,
CHARINDEX(',',replace(t.data,' ',',')+',',number)-number) as data,
ROW_NUMBER() over(partition by id,date order by @@servername) rownum
from tb t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+replace(t.data,' ',','),s.number,1) = ','
)
select @sql = @sql +
',max(case when rownum = '+CAST(rownum as varchar) +
' then data else null end) as [data'+CAST(rownum as varchar)+']'
from t
group by rownum
select @sql = ';with t
as
(
select id,
date,
SUBSTRING(replace(t.data,'' '','',''), number ,
CHARINDEX('','',replace(t.data,'' '','','')+'','',number)-number) as data,
ROW_NUMBER() over(partition by id,date order by @@servername) rownum
from tb t,master..spt_values s
where s.number >=1
and s.type = ''P''
and SUBSTRING('',''+replace(t.data,'' '','',''),s.number,1) = '',''
)'+
'select id,date'+@sql+
'from t group by id,date'
exec( @sql)
/*
id date data1 data2 data3 data4 data5
1 2014-01-20 11:36:08.943 1asd 2assd 3asd 4das 5gfsd
2 2014-01-20 11:36:08.943 6asd 7assd 8asd 9das 10agfsd
*/
[/quote]
说了,不借助其他手段。拼接字符串也属于其他手段。
如果拼接字符串,直接replace字符为 "as 列名,"就ok,不用你这么麻烦。
drop table tb
go
create table tb(ID int,date datetime,data varchar(1000))
insert into tb
select 1 ,'2014-01-20 11:36:08.943', '1asd 2assd 3asd 4das 5gfsd' union all
select 2 ,'2014-01-20 11:36:08.943', '6asd 7assd 8asd 9das 10agfsd'
go
declare @sql varchar(8000)
set @sql = ''
;with t
as
(
select id,
date,
SUBSTRING(replace(t.data,' ',','), number ,
CHARINDEX(',',replace(t.data,' ',',')+',',number)-number) as data,
ROW_NUMBER() over(partition by id,date order by @@servername) rownum
from tb t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+replace(t.data,' ',','),s.number,1) = ','
)
select @sql = @sql +
',max(case when rownum = '+CAST(rownum as varchar) +
' then data else null end) as [data'+CAST(rownum as varchar)+']'
from t
group by rownum
select @sql = ';with t
as
(
select id,
date,
SUBSTRING(replace(t.data,'' '','',''), number ,
CHARINDEX('','',replace(t.data,'' '','','')+'','',number)-number) as data,
ROW_NUMBER() over(partition by id,date order by @@servername) rownum
from tb t,master..spt_values s
where s.number >=1
and s.type = ''P''
and SUBSTRING('',''+replace(t.data,'' '','',''),s.number,1) = '',''
)'+
'select id,date'+@sql+
'from t group by id,date'
exec( @sql)
/*
id date data1 data2 data3 data4 data5
1 2014-01-20 11:36:08.943 1asd 2assd 3asd 4das 5gfsd
2 2014-01-20 11:36:08.943 6asd 7assd 8asd 9das 10agfsd
*/