请教一个sql语句...

[freedy_hu]

字段名: cn |objectClass
字段值：Administrator |top,person,orgPerson,user

根据objcetClass值的逗号生成下列：

字段名: cn |objectClass |col1|col2 |col3 |col4
字段值：Administrator |top,person,orgPerson,user |top |person |orgPerson |user

注意：字段objectClass的值不定，也就是n个","分割

[allright_flash]

又是行转列，找高手，高手们解决了不下于100次了

[freedy_hu]

给个提示，谢谢！

[zzz1975]

老大的例子我试验了不错，给你共享
--建立测试环境
Create Table 表(year varchar(10),name varchar(10),value varchar(10))
--插入数据
insert into 表
select '2002','a','1' union
select '2002','b','4' union
select '2002','c','5' union
select '2003','a','7' union
select '2003','b','5' union
select '2003','c','4' union
select '2004','a','4'
select * from 表
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT name'
SELECT @SQL= @SQL+
',sum(CASE WHEN year = ''' + year + ''' THEN value else 0 END) [' + year + ']'
FROM (SELECT DISTINCT year FROM 表) A
SET @SQL=@SQL+' FROM 表 GROUP BY name'
exec (@SQL)


--删除测试环境
Drop Table 表
/*
name 2002 2003 2004
a 1 7 4
b 4 5 0
c 5 4 0

[freedy_hu]

好像不行吧，这个的列值是不定长的，也就是要转为不固定个数的列

[懒得去死]

http://www.xue163.com/22997.html

[poemdreamer]

select cn ,objectClass,
substring_index('top,person,orgPerson,user',',',1) as col1,
substring_index(substring_index('top,person,orgPerson,user',',',2),',',-1) as col2,
substring_index(substring_index('top,person,orgPerson,user',',',3),',',-1) as col3,
substring_index(substring_index('top,person,orgPerson,user',',',4),',',-1) as col4
from table_name ;

[poemdreamer]

正解

select cn ,objectClass,
substring_index(objectClass,',',1) as col1,
substring_index(substring_index(objectClass,',',2),',',-1) as col2,
substring_index(substring_index(objectClass,',',3),',',-1) as col3,
substring_index(substring_index(objectClass,',',4),',',-1) as col4
from table_name ;

[skyairmj]

zzz1975(大葱炒肉，也就是葱爆肉) 的方法是最佳的

但可惜是sqlserver上的语句