数据库SQL大全

转贴自  http://hi.baidu.com/nepiphyllum/blog/item/5cee9644938be220cffca331.html

 

一、连接列值
db2/oracle/postgresql
select name(字段)||' Works AS a '（文字）||job as msg from emp where deptno=10;

mysql
select concat(name,' works as a',job) as msg from emp where deptno=10;

sql server
select name+' works as a '+job as msg from emp where deptno=10;

二、使用条件逻辑
select name,salary,
     case when salary<=2000 then 'low'
     case when salary>=4000 then 'over'
       else 'ok'
     end as status
from emp

三、限制返回的行数
db2
select * from emp fetch first 5 rows only

mysql/postgresql
select * from emp limit 5

oracle
select * from emp rownum<=5

sql server
select top 5 * from emp

四、随机返回记录
db2
select name,job from emp order by rahnd() fetch first 5 rows only

mysql
select name,job from emp order by rand() limit 5

postgresql
select * from emp order by random() limit 5

oracle
select * from (select name,job from emp order by dbms_random.value()) where rownum<=5

sql server
select top 5 name,job from emp order by newid()

五、将空值转换成实际值
select coalesce(comm,0) from emp
不为空则返回comm值，空则返回0，comm类型与0类型必须一致

六、按子串排序(取消后面2位)
db2/mysql/oracle/postgresql
select name,job from emp order by substr(job,length(job)-2)

sql server
select name,job from emp order by substring(job,len(job)-2,2)

七、对字母数字混合的数据排序
oracle/postgresql
/*order by deptno(数字)*/
select data from emp v order by replace (data,replace(translate(data,'0123456789','##########'),'#',''),'')

/*order by name(字母)*/
select data from emp order by replace(translate(data,'0123456789','##########'),'#','')

db2
/*order by deptno(数字)*/
select * from (select ename||' '||cast(deptno as char(2)) as data fromemp ) v order by replace(data,replace(translate(data,'##########','0123456789'),'#',''),'')

/*order by name(字母)*/
select * from (select name||' '||cast(deptno as char(2)) as data fromemp v order by replace(translate(data,'##########','0123456789'),'#','')

mysql/sqlserver
当前不支持translate函数，无解决方案

八、处理排序空值
db2/mysql/postgresql/sqlserver
select name,sal,comm from (select name,sal,comm case when comm is nullthen 0 else 1 end as is_null from emp) x order by is_null desc,comm

oracle
select name,sal,comm from emp order by comm nulls last//all nulls last
select name,sal,comm from emp order by comm nulls first//all nulls first

九、根据数据项的键排序
select name,sal,job,comm from emp order by case when job='salesman' then comm else sal end

十、记录集的叠加
//使用union子句相当于对使用union all子句的结果使用distinct
select ename as ename_and_dname,deptno from emp where deptno=10 unionall select '----------',null from t1 union all select dname,deptno fromdept

十一、从一个表红查找另一个表没有的值
db2/postgresql
select deptno from dept except select deptno from emp

oracle
select deptno from dept minus select deptno from emp

mysql/sqlserver
select deptno from dept where deptno not in (select deptno from emp)

十二、在一个表中查找与其他表不匹配的记录
db2/mysql/postgresql/sqlserver
select d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is null

oracle
select d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno is null

十三、向查询中增加联接而不影响其他联接
db2/mysql/postgresql/sqlserver
select e.ename,d.loc,eb.received from emp e join dept d on(e.deptno=d.deptno) left join emp_bonus eb on (e.empno=eb.empno) orderby 2

oracle
select e.ename,d.loc,eb.received from emp e,dept d,emp_bonus eb where e.deptno=d.deptno and e.empno=eb.empno (+) order by 2
select e.ename,d.loc,(select eb.received from emp_bonus eb whereeb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptnoorder by 2

十四、检测两个表中是否有相同的数据
解决原理：
1、首先，查找处表emp中存在而视图v中没有的行
2、然后合并（union all）在视图v中存在，而在表emp中没有的行

十五、识别和消除笛卡尔积
在from子句对表进行联接来返回正确的结果集：
select e.ename,d.loc from emp e,dept d where e.deptno=10 and d.deptno =e.deptno

十六、聚集与联接
/*只对不相同的工资求和*/
mysql/postgresql
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type=1then .1 when eb.type=2 then .2 else .3 end as bonus from empe,emp_bonus eb where e.empno=eb.empno and e.deptno=1) x group by deptno

db2/oracle/sqlserver
select distinct deptno,total_sal,total_bonus from (selecte.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) astotal_sal,e.deptno,sum(e.sal*case when eb.type=1 then .1 when eb.type=2then .2 else .3 end) over (partition by deptno) as total_bonus from empe,emp_bonus eb where e.empno=eb.empno and e.deptno=10) x

十七、聚集与外联接
/*只对部门10中不同的工资进行汇总*/
db2/mysql/postgresql/sqlserver
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type isnull then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 endas bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno)where e.deptno=10) group by deptno

select distinct deptno,total_sal,total_bonus from (selecte.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) astotal_sal,e.deptno,sum(e.sal*case when eb.type is null then 0 wheneb.type=1 then .1 when eb.type =2 then .2 else .3 end) over (partitionby deptno) as total_bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) x

oracle
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type isnull then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 endas bonus from emp e,emp_bonus eb where e.empno=eb.empno (+) ande.deptno=10) group by deptno

十八、从多个表中返回丢失的数据
db2/mysql/postgresql/sqlserver
select d.deptno,d.dname,e.ename from dept d full outer join emp e on (d.deptno=e.deptno)

select d.deptno,d.dname,e.ename from dept d right outer join emp eon(d.deptno=e.deptno) union select d.deptno,d.dname,e.ename from dept dleft outer join emp e on (d.deptno=e.deptno)

oracle
select d.deptno,d.dname,e.ename from dept d,emp e whered.deptno=e.deptno (+) union select d.deptno,d.dname,e.ename from deptd,emp e where d.deptno(+)=e.deptno

十九、在运算和比较时使用null值
select ename,comm from emp where coalesce(comm,0)<(select comm from emp where ename='WARD')

二十、从一个表向另外的表中复制行
insert into dept_east (deptno,dname,loc) select deptno,dname,loc from dept where loc in('NEW YORK','BOSTON')

二十一、复制表定义
db2
create table dept_2 like dept

oracle/mysql/postgresql
create table dept_2 as select * from dept where 1=0

sqlserver
select * into dept_2 from dept where 1=0

二十二、一次向多个表中插入记录
oracle
insert all when loc in('NEW YORK','BOSTON') then into dept_east (deptno,dname,loc) values(deptno,dname,loc)
when loc='CHICAGO' then into dept_mid (deptno,dname,loc) values(deptno,dname,loc)
else into dept_west (deptno,dname,loc) values(deptno,dname,loc)
select deptno,dname,loc from dept

db2
insert into (select * from dept_west union all select * from dept_east union all select * from dept_mid) select * from dept

mysql/postgresql/sqlserver
不支持多表插入操作

二十三、阻止对某几列插入
在表中创建一个视图，该视图将只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行
create view new_emps as select empno,ename,job from emp

二十四、用其他表中的值更新
db2/mysql
update emp e set(e.sal,e.comm)=(select ns.sal,ns.sal/2 from new_sal nswhere ns.deptno=e.deptno) where exists(select nul from new_sa ns wherens.deptno=e.deptno)

oracle
update(select e.sal as emp_sal,e.comm as emp_comm,ns.sal asns_sal,ns.sal/2 as ns_comm frm emp e,new_sal ns wheree.deptno=ns.deptno) set emp_sal=ns_sal,emp_comm=ns_comm

postgresql
update emp set sal=ns.sal,comm=ns.sal/2 from new_sal ns where ns.deptno=emp.deptno

sqlserver
update e set e.sal=ns.sal,e.comm=ns.sal/2 from emp e,new_sal ns where ns.deptno=emp.deptno

二十五、合并记录
oracle
merge into emp_commission ec using(select * from emp) empon(ec.empno=emp.empno) when matched then update set ec.comm=1000 deletewhere (sal<2000) when not matched then insert(ec.empno,ec.ename,ec.deptno,ec.comm) values(emp.empno,emp.ename,emp.deptno,emp.comm)

二十六、删除违反参照完整性的记录
delete from emp where not exists(select * from dept where dept.deptno=emp.deptno)

delete from emp where deptno not in(select deptno from dept)

二十七、删除重复记录
delete from dupes where id not in (select min(id) from dupes group by name(需要判断重复的字段))

二十八、删除从其他表引用的记录
delete from emp where deptno in (select deptno from dept_accidents group by deptno having count(*)>=3)

（以下模式名schema为smeagol）
二十九、列出模式中的表
db2
select tabname from syscat.table where tabschema='smeagol'

oracle
select table_name from all_tables where owner='smeagol'

postgresql/mysql/sqlserver
select tablename from information_schema.tables where table_schema='smeagol'

三十、列出表的列
db2
select colname,typename,colno from syscat.columns where tablename='emp' and tabschema='smeagol'

oracle
select column_name,data_type,column_id from all_tab_columns where owner='smeagol' and table_name='emp'

postgresql/mysql/sqlserver
select column_name,data_type,ordinal_position from information_schema='smeagol' and table_name='emp'

三十一、列出表的索引列
db2
select a.tabname,b.indname,b.colname,b.colseq from syscat.indexesa,syscat.indexcoluse b where a.tabname='emp' and a.tabschema='smeagol'and a.indschema=b.indschema and a.indname=b.indname

oracle
select table_name,index_name,column_name,column_position fromsys.all_ind_columns where table_name='emp' and table_owner='smeagol'

postgresql
select a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexesa,information_schema.columns b where a.schemaname='smeagol' anda.tablename=b.table_name

mysql
show index from emp

sqlserver
select a.name table_name,b.name index_name,d.namecolumn_name,c.index_column_id from sys.tables a,sys.indexesb,sys.index_columns c,sys.columns d where a.object_id=b.object_id andb.object_id=c.object_id and b.index_id=c.index_id andc.object_id=d.object_id and c.column_id=d.column_id and a.name='emp'

三十二、列出表约束
db2
select a.tabname,a.constname,b.colname,a.type from syscat.tabconsta,syscat.columns b where a.tabname='emp' and a.tabschema='smeagol' anda.tabname=b.tabname and a.tabschema=b.tabschema

oracle
select a.table_name,a.constraint_name,b.column_name,a.constraint_typefrom all_constraints a,all_cons_columns b where a.table_name='emp' anda.owner='smeagol' and a.table_name=b.table_name and a.owner=b.owner anda.constraint_name=b.constraint_name

postgresql/mysql/sqlserver
select a.table_name,a.constraint_name,b.column_name,a.constraint_typefrom information_schema.table_constraintsa,information_schema.key_column_usage b where a.table_name='emp' anda.table_schema='smeagol' and a.table_name=b.table_name anda.table_schema=b.table_schema and a.constraint_name=b.constraint_name

三十三、列出没有相应索引的外键
db2
select fkeys.tabname,fkeys.constname,fkeys.colname,ind_cols.indnamefrom (select a.tabschema,a.tabname,a.constname,b.colname fromsyscat.tabconst a,syscat.keycoluse b where a.tabname='emp' anda.tabschema='smeagol' and a.type='f' and a.tabname=b.tabname anda.tabschema=b.tabschema) fkeys left join (selecta.tabschema,a.tabname,a.indname,b.colname from syscat.indexesa,syscat.indexcoluse b where a.indschema=b.indschema anda.indname=b.indname) ind_cols on(fkeys.tabschema=ind_cols.tabschema andfkeys.tabname=ind_cols.tabname and fkeys.colname=ind_cols.colname)whereind_cols.indname is null

oracle
select a.table_name,a.constraint_name,a.column_name,c.index_name fromall_cons_columns a,all_constraints b,all_ind_columns c wherea.table_name='emp' and a.owner='smeagol' and b.constraint_type='r' anda.owner=b.owner and a.table_name=b.table_name anda.constraint_name=b.constraint_name and a.owner=c.table_owner(+) anda.table_name=c.table_name(+) and a.column_name=c.column_name(+) andc.index_name is null

postgresql
selectfkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_namefrom (selecta.constraint_schema,a.table_name,a.constraint_name,a.column_name frominformation_schema.key_column_usagea,information_schema.referential_constraints b where a.table_name='emp'and a.constraint_schema='smeagol' anda.constraint_name=b.constraint_name anda.constraint_schema=b.constraint_schema) fkeys left join (selecta.schemaname,a.tablename,a.indexname,b.column_name frompg_catalog.pg_indexes a,information_schema.columns b wherea.schemaname=b.table_schema and a.tablename=b.table_name) ind_colson(fkeys.constraint_schema=ind_cols.schemaname andfkeys.table_name=ind_cols.tablename andfkeys.column_name=ind_cols.column_name)where ind_cols.indexname is null

mysql
使用show index来检索索引信息，查询information——schema.key_column_usage列出指定表的外键

sqlserver
selectfkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_namefrom (select a.object_id,d.column_id,a.name table_name,b.nameconstraint_name,d.name column_name from sys.tables a joinsys.foreign_keys b on (a.name='emp' and a.object_id=b.parent_object_id)join sys.foreign_key_columns c on(b.object_id=c.constraint_object_id)join sys.columns d on (c.constraint_column_id=d.column_id anda.object_id=d.object_id) fkeys left join (select a.nameindex_name,b.object_id,b.column_id from sys.indexes a,sys.index_columnsb where a.index_id=b.index_id) ind_cols on(fkeys.object_id=ind_cols.object_id andfkeys.column_i=ind_cols.column_id) where ind_cols.index_name is null

三十四、使用sql来生成sql
oracle
select 'select count(*) from '||table_name||';' cnts from user_tables;

三十五、在oracle中描述数据字典视图
select table_name,comments from dictionary order by table_name

select column_name,comments from dict_columns where table_name='all_tab_columns'

三十六、遍历字符串
select substr(e.ename,iter.post,1) as c from (select ename from empwhere ename='king') e,(select id as pos from t10) iter whereiter.pos<=length(e.ename)

三十七、计算字符在字符串中出现的次数
select (length('10,clark,manager')-length(replace('10,clark,manager',',','')))/length(',') as cnt from t1

三十八、从字符串中删除不需要的字符
db2
select ename,replace(translate(ename,'aaaaa','AEIOU'),'a','')stripped1,sal,replace(cast(sal as char(4)),'0','') stripped2 from emp

mysql/sqlserver
selectename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','')as stripped1,sal,replace(sal,0,'') stripped2 from emp

oracle/postgresql
select ename,replace(translate(ename,'AEIOU','aaaa'),'a') as stripped1,sal,replace(sal,0,'') as stripped2 from emp

三十九、将字符和数字数据分离
db2
select replace(translate(data,'0000000000','0123456789'),'0','')ename,cast(replace(translate(lower(data),repeat('z',26),'abcdefghijklmnopqrstuvwxyz'),'z','')as integer) sal from (select ename||cast(sal as char(4)) data from emp)x

oracle
select replace(translate(data,'0123456789','0000000000'),'0')ename,to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z'))sal from (select ename||sal data from emp)

postgresql
select replace(translate(data,'0123456789','0000000000'),'0','') asename,cast(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z','')as integer) as sal from (select ename||sal as data from emp) x

四十、判断字符串是不是字母数字型的
db2
select data from view wheretranslate(lower(data),repeat('a',36),'0123456789abcdefghijklmnopqrstuvwxyz')=repeat('a',length(data))

mysql
create view V as select ename as data from emp where deptno=10 unionall select concat(ename,',$',sal,'.00') as data from emp wheredeptno=20 union all select concat(ename,deptno) as data from emp wheredeptno=30
select data from V where data regexp '[^0-9a-zA-Z]'=0

oracle/postgresql
select data from V wheretranslate(lower(data),'0123456789abcdefghijklmnopqrstuvwxyz',rpad('a',36,'a'))=rpad('a',length(data),'a')

sqlserver
select data from (select v.data,iter.pos,substring(v.data,iter.pos,1)c,ascii(substring(v.data,iter.pos,1)) val from v,(select id as pos fromt100) iter where iter.pos<=len(v.data)) x group by data havingmin(val) between 48 and 122

四十一、提取姓名的大写首字母缩写
db2
select replace(replace(translate(replace('StewieGriffin','.',''),repeat('#',26),'abcdefghijklmnopqrstuvwxyz'),'#',''),'','.')||'.' from dual

mysql
select case when cnt=2 then trim(trailing '.' fromconcat_ws('.',substr(substring_index(name,'',1),1,1),substr(name,length(substring_index(name,'',1))+2,1),substr(substring_index(name,' ',-1),1,1),'.')) elsetrim(trailing '.' from concat_ws('.',substr(substring_index(name,'',1),1,1),substr(substring_index(name,' ',-1),1,1))) end as initialsfrom (select name,length(name)-length(replace(name,' ','')) as cnt from(select replace('Stewie Griffin','.','') as name from dual ) y ) x

oracle/postgresql
select replace(replace(translate(replace('StewieGriffin','.',''),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#',''),'','.')||'.' from dual

四十二、按字符串中的部分内容排序
db2/oracle/mysql/postgresql
select ename from emp order by substr(ename,length(ename)-1,2)

sqlserver
select ename from emp order by substring(ename,len(ename)-1,2)

四十三、按字符串中的数值排序
db2
select data from V order bycast(replace(translate(data,repeat('#',length(data)),replace(translate(data,'##########','0123456789'),'#','')),'#','')as integer)

oracle
select data from V order byto_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#'))

postgresql
select data from V order bycast(replace(translate(data,replace(translate(data,'0123456789','##########'),'#',''),rpad('#',20,'#')),'#','')as integer)

四十四、根据表中的行创建一个分割列表
db2
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over(partition by deptno), cast(ename as varchar(100)),empno,1 from empunion all select x.deptno,x.cnt,x.list||','||e.name,e.empno,x.len+1from emp e,x where e.deptno=x.deptno and e.empno>x.empno) selectdeptno,list from x where len=cnt

mysql
select deptno,group_concat(ename order by empno separator,',') as emps from emp group by deptno

oracle
select deptno,ltrim(sys_connect_by_path(ename,','),',') emps from(select deptno,ename,row_number() over (partition by deptno order byempno) rn,count(*) over (partition by deptno) cnt from emp) wherelevel=cnt start with rn=1 connect by prior deptno=deptno and priorrn=rn-1

postgresql
select deptno,rtrim(max(case when pos=1 then emps else ''end)||max(case when pos=2 then emps else '' end)||max(case when pos=3then emps else '' end)||max(case when pos=4 then emps else ''end)||max(case when pos=5 then emps else '' end)||max(case when pos=6then emps else '' end),',') as emps from (select a.deptno,a.ename||','as emps,d.cnt,(select count(*) from emp b where a.deptno=b.deptno andb.empno<=empno ) as pos from emp a,(select deptno,count(ename) ascnt from emp group by deptno) d where d.deptno=a.deptno) x group bydeptno order by 1

sqlserver
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over(partition by deptno), cast(ename as varchar(100)),empno,1 from empunion all select x.deptno,x.cnt,cast(x.list+','+e.name asvarchar(100)),e.empno,x.len+1 from emp e,x where e.deptno=x.deptno ande.empno>x.empno) select deptno,list from x where len=cnt order by 1

四十五、将分隔数据转换为多值IN列表
db2
select empno,ename,sal,deptno from emp where empno in (selectcast(substr(c,2,locate(',',c,2)-2) as integer) empno from (selectsubstr(csv.emps,cast(iter.pos as integer)) as c from (select','||'7654,7698,7782,7788'||',' emps from t1 ) csv,(select id as posfrom t100) iter where iter.pos<=length(csv.emps)) x wherelength(c)>1 and substr(c,1,1)=',' ) y

mysql
select empno,ename,sal,deptno from emp where empno in (selectsubstring_index(substring_index(list.vals,',',iter.pos),',',-1) empnofrom (select id pos from t10) as iter,(select '7654,7698,7782,7788' asvals from t1) list whereiter.pos<=(length(list.vals)-length(replace(list.vals,',','')))+1) x

oracle
select empno,ename,sal,deptno from emp where empno in (selectto_number(rtrim(substr(emps,instr(emps,',',1,iter.pos)+1,instr(emps,',',1,iter.pos+1)-instr(emps,',',1,iter.pos)),','))emps from (select ','||'7654,7698,7782,7788'||',' emps from t1)csv,(select rownum pos from emp) iter whereiter.pos<=((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1

postgresql
select empno,ename,sal,deptno from emp where empno in (select cast(empno as integer) as empno from (selectsplit_part(list.vals,',',iter.pos) as empno from (select id as pos fromt10) iter,(select ','||'7654,7698,7782,7788'||',' as vals from t1) listwhere iter.pos<=length(list.vals)-length(replace(ist.vals,',','')))z where length(empno)>0) x

sqlserver
select empno,ename,sal,deptno from emp where empno in (selectsubstring(c,2,charindex(',',c,2)-2) as empno from (selectsubstring(csv.emps,iter.pos,len(csv.emps)) as c from (select','+'7654,7698,7782,7788'+',' as emps from t1) csv,(select id as posfrom t100) iter where iter.pos<=len(csv.emps)) x where len(c)>1and substring(c,1,1)=',') y

四十六、按字母顺序排列字符串
db2
select ename,max(case when pos=1 then c else '' end)||max(case whenpos=2 then c else '' end)||max(case when pos=3 then c else ''end)||max(case when pos=4 then c else '' end)||max(case when pos=5 thenc else '' end)||max(case when pos=6 then c else '' end) from (selecte.ename,cast(substr(e.ename,iter.pos,1) as varchar(100))c,cast(row_number() over (partition by e.ename order bysubstr(e.ename,iter.pos,1)) as integer) pos from emp e,(selectcast(row_number() over() as integer) pos from emp) iter whereiter.pos<=length(e.ename)) x group by ename

mysql
select ename,group_concat(c order by c separator '') from ( selectename,substr(a.ename,iter.pos,1) c from emp a,(select id pos from t10)iter where iter.pos<=length(a.ename)) x group by ename

oracle
select old_name,new_name from (selectold_name,replace(sys_connect-by_path(c,' '),' ') new_name from (selecte.ename old_name,row_number() over(partition by e.ename order bysubstr(e.ename,iter.pos,1)) rn,substr(e.ename,iter.pos,1) c from empe,(select rownum pos from emp) iter where iter.pos<=length(e.ename)order by 1) x start with rn=1 connect by prior rn=rn-1 and priorold_name=old_name) where length(old_name)=length(new_name)

postgresql
必须先遍历每个字符串，先要知道姓名的最大长度

sqlserver
select ename,max(case when pos=1 then c else '' end)+max(case whenpos=2 then c else '' end)+max(case when pos=3 then c else ''end)+max(case when pos=4 then c else '' end)+max(case when pos=5 then celse '' end)+max(case when pos=6 then c else '' end) from (selecte.ename,substring(e.ename,iter.pos,1) as c,row_number() over (partitionby e.ename order by substring(e.ename,iter.pos,1)) as pos from empe,(select row_number() over(order by ename) as pos from emp) iter whereiter.pos<=len(e.ename)) x group by ename