之前问过,高人josy帮助解答过,但是没能在其基础之上改过来,所以也把之前问的答案附在这边declare @s varchar(1000),@sql varchar(8000) select @s=isnull(@s+'+'+'''$''+','')+'ltrim('+name+')', @sql=isnull(@sql+' union all ','')+' select 行号,行号+''$''+'+@s+' as 列值 from tb' from syscolumns where id=object_id('tb') and name!='行号'--print @sqlexec (@sql+' order by 行号')/** 行号 列值 ---- ------------------------------------------------------ XZ XZ$11 XZ XZ$11$21 XZ XZ$11$21$31 XZ XZ$11$21$31$41 ZJ ZJ$11 ZJ ZJ$11$21$31$41 ZJ ZJ$11$21$31 ZJ ZJ$11$21(8 行受影响) **/
select 部门 岗位1 from tb uinon all select 部门 岗位2 from tb uinon all select 部门 岗位3 from tb uinon all select 部门 岗位4 from tb
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB ( 部门 NVARCHAR(25), 岗位1 NVARCHAR(25), 岗位2 NVARCHAR(25), 岗位3 NVARCHAR(25), 岗位4 NVARCHAR(25) ) INSERT INTO TB SELECT '[ZJ]总经办','[11]总经理','[21]副经理','[31]','[41]' UNION ALL SELECT '[XZ]行政处','[11]','[21]','[31]主管','[41]副主管' declare @s1 varchar(8000),@sql varchar(8000),@s2 varchar(8000),@s3 varchar(8000) select @s1=isnull(@s1+'+'+'''$''+','')+'SUBSTRING(ltrim('+name+'),CHARINDEX(''['',ltrim('+name+'))+1,CHARINDEX('']'',ltrim('+name+'))-CHARINDEX(''['',ltrim('+name+'))-1)', @s2=isnull(@s2+'+','')+'RIGHT(ltrim('+name+'),LEN(ltrim('+name+'))-CHARINDEX('']'',ltrim('+name+')))', @s3='RIGHT(ltrim('+name+'),LEN(ltrim('+name+'))-CHARINDEX('']'',ltrim('+name+')))<>'''' AND RIGHT(ltrim('+name+'),LEN(ltrim('+name+'))-CHARINDEX('']'',ltrim('+name+'))) IS NOT NULL', @sql=isnull(@sql+' union all ','')+' select SUBSTRING(部门,CHARINDEX(''['',部门)+1,CHARINDEX('']'',部门)-CHARINDEX(''['',部门)-1) AS 部门,SUBSTRING(部门,CHARINDEX(''['',部门)+1,CHARINDEX('']'',部门)-CHARINDEX(''['',部门)-1)+''$''+'+@s1+' as 岗位编号,RIGHT(部门,LEN(部门)-CHARINDEX('']'',部门))+'+@s2+' AS 岗位名称 from tb WHERE '+@s3+'' from syscolumns where id=object_id('tb') and name!='部门' print @sql exec (@sql+' order by 岗位编号') ---------------------------------- 部门 岗位编号 岗位名称 XZ XZ$11$21$31 行政处主管 XZ XZ$11$21$31$41 行政处主管副主管 ZJ ZJ$11 总经办总经理 ZJ ZJ$11$21 总经办总经理副经理 楼主试试吧,好麻烦,膜拜树哥
用静态语句处理简单明了create table tb (dept nvarchar(20),pos1 nvarchar(20),pos2 nvarchar(20),pos3 nvarchar(20),pos4 nvarchar(20)); go insert into tb values (N'[ZJ]总经办',N'[11]总经理',N'[21]副经理',N'[31]',N'[41]'); insert into tb values (N'[XZ]行政处',N'[11]',N'[21]',N'[31]主管',N'[41]副主管'); goselect left(dept,charindex(']',dept)) dept, (case v.number when 1 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1)) when 2 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))+'$'+left(pos2,charindex(']',pos2)) when 3 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))+'$'+left(pos2,charindex(']',pos2))+'$'+left(pos3,charindex(']',pos3)) when 4 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))+'$'+left(pos2,charindex(']',pos2))+'$'+left(pos3,charindex(']',pos3))+'$'+left(pos4,charindex(']',pos4)) end) [pos_no], (case v.number when 1 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos1,len(pos1)-charindex(']',pos1)),'') when 2 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos2,len(pos2)-charindex(']',pos2)),'') when 3 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos3,len(pos3)-charindex(']',pos3)),'') when 4 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos4,len(pos4)-charindex(']',pos4)),'') end) [pos_name] from master..spt_values v,tb where v.number between 1 and 4 -- 列数 and v.type='P'; /* [ZJ] [ZJ]$[11] 总经办总经理 [ZJ] [ZJ]$[11]$[21] 总经办副经理 [ZJ] [ZJ]$[11]$[21]$[31] NULL [ZJ] [ZJ]$[11]$[21]$[31]$[41] NULL [XZ] [XZ]$[11] NULL [XZ] [XZ]$[11]$[21] NULL [XZ] [XZ]$[11]$[21]$[31] 行政处主管 [XZ] [XZ]$[11]$[21]$[31]$[41] 行政处副主管 */drop table tb;
select
@s=isnull(@s+'+'+'''$''+','')+'ltrim('+name+')',
@sql=isnull(@sql+' union all ','')+' select 行号,行号+''$''+'+@s+' as 列值 from tb'
from
syscolumns
where
id=object_id('tb')
and
name!='行号'--print @sqlexec (@sql+' order by 行号')/**
行号 列值
---- ------------------------------------------------------
XZ XZ$11
XZ XZ$11$21
XZ XZ$11$21$31
XZ XZ$11$21$31$41
ZJ ZJ$11
ZJ ZJ$11$21$31$41
ZJ ZJ$11$21$31
ZJ ZJ$11$21(8 行受影响)
**/
uinon all
select 部门 岗位2 from tb
uinon all
select 部门 岗位3 from tb
uinon all
select 部门 岗位4 from tb
GO
CREATE TABLE TB
(
部门 NVARCHAR(25),
岗位1 NVARCHAR(25),
岗位2 NVARCHAR(25),
岗位3 NVARCHAR(25),
岗位4 NVARCHAR(25)
)
INSERT INTO TB
SELECT '[ZJ]总经办','[11]总经理','[21]副经理','[31]','[41]' UNION ALL
SELECT '[XZ]行政处','[11]','[21]','[31]主管','[41]副主管' declare @s1 varchar(8000),@sql varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select
@s1=isnull(@s1+'+'+'''$''+','')+'SUBSTRING(ltrim('+name+'),CHARINDEX(''['',ltrim('+name+'))+1,CHARINDEX('']'',ltrim('+name+'))-CHARINDEX(''['',ltrim('+name+'))-1)',
@s2=isnull(@s2+'+','')+'RIGHT(ltrim('+name+'),LEN(ltrim('+name+'))-CHARINDEX('']'',ltrim('+name+')))',
@s3='RIGHT(ltrim('+name+'),LEN(ltrim('+name+'))-CHARINDEX('']'',ltrim('+name+')))<>'''' AND RIGHT(ltrim('+name+'),LEN(ltrim('+name+'))-CHARINDEX('']'',ltrim('+name+'))) IS NOT NULL',
@sql=isnull(@sql+' union all ','')+' select SUBSTRING(部门,CHARINDEX(''['',部门)+1,CHARINDEX('']'',部门)-CHARINDEX(''['',部门)-1) AS 部门,SUBSTRING(部门,CHARINDEX(''['',部门)+1,CHARINDEX('']'',部门)-CHARINDEX(''['',部门)-1)+''$''+'+@s1+' as 岗位编号,RIGHT(部门,LEN(部门)-CHARINDEX('']'',部门))+'+@s2+' AS 岗位名称 from tb WHERE '+@s3+''
from
syscolumns
where
id=object_id('tb')
and
name!='部门'
print @sql
exec (@sql+' order by 岗位编号')
----------------------------------
部门 岗位编号 岗位名称
XZ XZ$11$21$31 行政处主管
XZ XZ$11$21$31$41 行政处主管副主管
ZJ ZJ$11 总经办总经理
ZJ ZJ$11$21 总经办总经理副经理
楼主试试吧,好麻烦,膜拜树哥
(dept nvarchar(20),pos1 nvarchar(20),pos2 nvarchar(20),pos3 nvarchar(20),pos4 nvarchar(20));
go
insert into tb values (N'[ZJ]总经办',N'[11]总经理',N'[21]副经理',N'[31]',N'[41]');
insert into tb values (N'[XZ]行政处',N'[11]',N'[21]',N'[31]主管',N'[41]副主管');
goselect left(dept,charindex(']',dept)) dept,
(case v.number
when 1 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))
when 2 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))+'$'+left(pos2,charindex(']',pos2))
when 3 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))+'$'+left(pos2,charindex(']',pos2))+'$'+left(pos3,charindex(']',pos3))
when 4 then left(dept,charindex(']',dept))+'$'+left(pos1,charindex(']',pos1))+'$'+left(pos2,charindex(']',pos2))+'$'+left(pos3,charindex(']',pos3))+'$'+left(pos4,charindex(']',pos4)) end) [pos_no],
(case v.number
when 1 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos1,len(pos1)-charindex(']',pos1)),'')
when 2 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos2,len(pos2)-charindex(']',pos2)),'')
when 3 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos3,len(pos3)-charindex(']',pos3)),'')
when 4 then right(dept,len(dept)-charindex(']',dept))+nullif(right(pos4,len(pos4)-charindex(']',pos4)),'') end) [pos_name]
from master..spt_values v,tb
where v.number between 1 and 4 -- 列数
and v.type='P';
/*
[ZJ] [ZJ]$[11] 总经办总经理
[ZJ] [ZJ]$[11]$[21] 总经办副经理
[ZJ] [ZJ]$[11]$[21]$[31] NULL
[ZJ] [ZJ]$[11]$[21]$[31]$[41] NULL
[XZ] [XZ]$[11] NULL
[XZ] [XZ]$[11]$[21] NULL
[XZ] [XZ]$[11]$[21]$[31] 行政处主管
[XZ] [XZ]$[11]$[21]$[31]$[41] 行政处副主管
*/drop table tb;