原表数据格式: fields fvalues f1,f2,f3,f4... value1,value2,value3,value4...
把fields中的字符串转换成动态的列名,而fvalues字段中值对应转换格式为: f1 f2 f3 f4 .....
value1 value2 value3 value4 ......
把fields中的字符串转换成动态的列名,而fvalues字段中值对应转换格式为: f1 f2 f3 f4 .....
value1 value2 value3 value4 ......
颜色,尺码.. 红色,37..转换后颜色 尺码 .. (动态列名)红色 37 .. (对应值)
create table wb4888888
(f1 char(6),f2 char(6),value1 char(6),value2 char(6))insert into wb4888888
select '颜色','尺码','红色','37'
select * from
(select f1,f2
from wb4888888
union all
select value1,value2
from wb4888888) tf1 f2
------ ------
颜色 尺码
红色 37 (2 row(s) affected)
create table wb4888888
(fields char(16), fvalues char(16))insert into wb4888888
select '颜色,尺码','红色,37'select * from wb4888888fields fvalues
---------------- ----------------
颜色,尺码 红色,37
with t3 as
(select t1.fn,t1.field,t2.fvalue
from
(select row_number() over(order by getdate()) fn,
substring(a.fields,b.number,charindex(',',a.fields+',',b.number)-b.number) field
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.fields,b.number,1)=',') t1
inner join
(select row_number() over(order by getdate()) vn,
substring(a.fvalues,b.number,charindex(',',a.fvalues+',',b.number)-b.number) fvalue
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.fvalues,b.number,1)=',') t2
on t1.fn=t2.vn
)
select [1] col1,[2] col2 from
(
select fn,t5.x,t5.c
from t3
unpivot
(c for x in (field,fvalue)) t5
) t6
pivot(max(c) for fn IN ([1], [2])) tcol1 col2
---------------- ----------------
颜色 尺码
红色 37 (2 row(s) affected)
declare @sql varchar(max)
select @sql = isnull(@sql + '],[' , '') + fields from #table
set @sql = '[' + @sql + ']'
exec ('select * from (select * from #table) a pivot (max([value]) for fields in (' + @sql + ')) b')
create table wb4888888
(fields char(16), fvalues char(16))insert into wb4888888
select '颜色,尺码','红色,37'select * from wb4888888fields fvalues
---------------- ----------------
颜色,尺码 红色,37
with t3 as
(select t1.fn,t1.field,t2.fvalue
from
(select row_number() over(order by getdate()) fn,
substring(a.fields,b.number,charindex(',',a.fields+',',b.number)-b.number) field
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.fields,b.number,1)=',') t1
inner join
(select row_number() over(order by getdate()) vn,
substring(a.fvalues,b.number,charindex(',',a.fvalues+',',b.number)-b.number) fvalue
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.fvalues,b.number,1)=',') t2
on t1.fn=t2.vn
)
select max([颜色]) '颜色',max([尺码]) '尺码'
from t3
pivot(max(fvalue) for field IN ([颜色],[尺码])) t5
颜色 尺码
---------------- ----------------
红色 37 (1 row(s) affected)
感谢楼上的解答,以下是我想要的查询结果颜色 尺码
---------------- ----------------
而您贴出的代码是静态的,只有两列。fields fvalues
颜色,尺码..N个 红色,37..N个
(fields char(30), fvalues char(30))insert into wb4888888
select '颜色,尺码,属性1,属性2,属性3','红色,37,值1,值2,值3'select * from wb4888888fields fvalues
------------------------------ ------------------------------
颜色,尺码,属性1,属性2,属性3 红色,37,值1,值2,值3 declare @sql varchar(6000),@s1 varchar(200),@s2 varchar(200)select @s1='['+replace(replace(fields,' ',''),',','],[')+']' from wb4888888select @s2=cast(
(select ' max(['+replace(field,' ','')+']) '''+replace(field,' ','')+''',' from
(select substring(a.fields,b.number,charindex(',',a.fields+',',b.number)-b.number) field
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.fields,b.number,1)=',') t0
for xml path('')) as varchar(200))select @sql='
with t3 as
(select t1.fn,t1.field,t2.fvalue
from
(select row_number() over(order by getdate()) fn,
substring(a.fields,b.number,charindex('','',a.fields+'','',b.number)-b.number) field
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]=''P'' and substring('',''+a.fields,b.number,1)='','') t1
inner join
(select row_number() over(order by getdate()) vn,
substring(a.fvalues,b.number,charindex('','',a.fvalues+'','',b.number)-b.number) fvalue
from wb4888888 a
inner join master.dbo.spt_values b
on b.[type]=''P'' and substring('',''+a.fvalues,b.number,1)='','') t2
on t1.fn=t2.vn
)
select '+substring(@s2,1,len(@s2)-1)+
'from t3 pivot(max(fvalue) for field IN ('+@s1+')) t5'exec(@sql)
颜色 尺码 属性1 属性2 属性3
----------- ----------- ----------- ----------- -----------
红色 37 值1 值2 值3 (1 row(s) affected)