如果固定只有三行Select Distinct tcode, (Select dcode from TableName Where tcode=A.tcode And dcode='d1') As dcode1, (Select dname from TableName Where tcode=A.tcode And dcode='d1') As dname1, (Select dcode from TableName Where tcode=A.tcode And dcode='d2') As dcode2, (Select dname from TableName Where tcode=A.tcode And dcode='d2') As dname2, (Select dcode from TableName Where tcode=A.tcode And dcode='d3') As dcode3, (Select dname from TableName Where tcode=A.tcode And dcode='d3') As dname3 from TableName A
create table #tt (tcode varchar(20), dcode varchar(20), dname varchar(20)) Insert #tt (tcode, dcode, dname) values ('t01', 'd1', '四川') Insert #tt (tcode, dcode, dname) values ('t01', 'd2', '云南') Insert #tt (tcode, dcode, dname) values ('t01', 'd3', '贵州') Select Distinct tcode, (Select dcode from #tt Where tcode=A.tcode And dcode='d1') As dcode1, (Select dname from #tt Where tcode=A.tcode And dcode='d1') As dname1, (Select dcode from #tt Where tcode=A.tcode And dcode='d2') As dcode2, (Select dname from #tt Where tcode=A.tcode And dcode='d2') As dname2, (Select dcode from #tt Where tcode=A.tcode And dcode='d3') As dcode3, (Select dname from #tt Where tcode=A.tcode And dcode='d3') As dname3 from #tt ASelect * from #tt Adrop table #tt
create table #tt (tcode varchar(20), dcode varchar(20), dname varchar(20)) Insert #tt (tcode, dcode, dname) values ('t01', 'd1', '四川') Insert #tt (tcode, dcode, dname) values ('t01', 'd2', '云南') Insert #tt (tcode, dcode, dname) values ('t01', 'd3', '贵州') Select Distinct tcode, (Select dcode from #tt Where tcode=A.tcode And dcode='d1') As dcode1, (Select dname from #tt Where tcode=A.tcode And dcode='d1') As dname1, (Select dcode from #tt Where tcode=A.tcode And dcode='d2') As dcode2, (Select dname from #tt Where tcode=A.tcode And dcode='d2') As dname2, (Select dcode from #tt Where tcode=A.tcode And dcode='d3') As dcode3, (Select dname from #tt Where tcode=A.tcode And dcode='d3') As dname3, (Select dcode from #tt Where tcode=A.tcode And dcode='d4') As dcode4, (Select dname from #tt Where tcode=A.tcode And dcode='d4') As dname4, (Select dcode from #tt Where tcode=A.tcode And dcode='d5') As dcode5, (Select dname from #tt Where tcode=A.tcode And dcode='d5') As dname5, (Select dcode from #tt Where tcode=A.tcode And dcode='d6') As dcode6, (Select dname from #tt Where tcode=A.tcode And dcode='d6') As dname6, (Select dcode from #tt Where tcode=A.tcode And dcode='d7') As dcode7, (Select dname from #tt Where tcode=A.tcode And dcode='d7') As dname7from #tt Adrop table #tt
七列的话 Select Distinct tcode, (Select dcode from TableName Where tcode=A.tcode And dcode='d1') As dcode1, (Select dname from TableName Where tcode=A.tcode And dcode='d1') As dname1, (Select dcode from TableName Where tcode=A.tcode And dcode='d2') As dcode2, (Select dname from TableName Where tcode=A.tcode And dcode='d2') As dname2, (Select dcode from TableName Where tcode=A.tcode And dcode='d3') As dcode3, (Select dname from TableName Where tcode=A.tcode And dcode='d3') As dname3, (Select dcode from TableName Where tcode=A.tcode And dcode='d4') As dcode4, (Select dname from TableName Where tcode=A.tcode And dcode='d4') As dname4, (Select dcode from TableName Where tcode=A.tcode And dcode='d5') As dcode5, (Select dname from TableName Where tcode=A.tcode And dcode='d5') As dname5, (Select dcode from TableName Where tcode=A.tcode And dcode='d6') As dcode6, (Select dname from TableName Where tcode=A.tcode And dcode='d6') As dname6, (Select dcode from TableName Where tcode=A.tcode And dcode='d7') As dcode7, (Select dname from TableName Where tcode=A.tcode And dcode='d7') As dname7 from TableName A
--生成测试数据 create table t1( tcode varchar(10), dcode varchar(10), dname varchar(10))insert into t1 select 't01','d1','四川' insert into t1 select 't01','d2','云南' insert into t1 select 't01','d3','贵州' insert into t1 select 't02','d4','湖南' insert into t1 select 't02','d5','福建' insert into t1 select 't03','d6','北京'
--执行查询过程 select identity(int,1,1) as id,0 as nid,* into #t from t1
update a set nid = (select count(*) from #t where tcode = a.tcode and id<=a.id) from #t a declare @nid int declare @sql varchar(8000)select @nid= max(nid),@sql='' from #twhile @nid>0 begin set @sql = ',dcode'+rtrim(@nid)+'=max(case when nid='+rtrim(@nid)+' then dcode end)'+ ',dname'+rtrim(@nid)+'=max(case when nid='+rtrim(@nid)+' then dname end)'+@sql set @nid = @nid - 1 endset @sql = 'select tcode'+@sql+' from #t group by tcode order by tcode'exec(@sql)
根据楼主的问题改进一下: ----------------------------------------------------------- --生成测试数据 create table t1( tcode varchar(10), dcode varchar(10), dname varchar(10))insert into t1 select 't01','d1','四川' insert into t1 select 't01','d2','云南' insert into t1 select 't01','d3','贵州' insert into t1 select 't01','d4','湖南' insert into t1 select 't01','d6','福建' insert into t1 select 't02','d1','上海' insert into t1 select 't02','d3','山东' insert into t1 select 't02','d4','湖北' insert into t1 select 't02','d5','广西' insert into t1 select 't03','d6','北京'
--执行查询过程 declare @nid int declare @sql varchar(8000)set @sql = ''select @sql = @sql + ',dcode'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+rtrim(dcode)+''' then dcode end)' + ',dname'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+rtrim(dcode)+''' then dname end)' from t1 group by dcodeset @sql = 'select tcode'+@sql+' from t1 group by tcode order by tcode'exec(@sql) --查询结果 tcode dcode1 dname1 dcode2 dname2 dcode3 dname3 dcode4 dname4 dcode5 dname5 dcode6 dname6 ----------------------------------------------------------------------------------------------- t01 d1 四川 d2 云南 d3 贵州 d4 湖南 NULL NULL d6 福建 t02 d1 上海 NULL NULL d3 山东 d4 湖北 d5 广西 NULL NULL t03 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL d6 北京
declare @nid int declare @sql varchar(8000)set @sql = 'select tcode'select @sql = @sql + ' ,dcode'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+ rtrim(dcode)+''' then dcode end)' + ',dname'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+ rtrim(dcode)+''' then dname end)' from t1 group by dcodeset @sql = @sql+' from t1 group by tcode order by tcode'exec(@sql)
Distinct
tcode,
(Select dcode from TableName Where tcode=A.tcode And dcode='d1') As dcode1,
(Select dname from TableName Where tcode=A.tcode And dcode='d1') As dname1,
(Select dcode from TableName Where tcode=A.tcode And dcode='d2') As dcode2,
(Select dname from TableName Where tcode=A.tcode And dcode='d2') As dname2,
(Select dcode from TableName Where tcode=A.tcode And dcode='d3') As dcode3,
(Select dname from TableName Where tcode=A.tcode And dcode='d3') As dname3
from TableName A
Insert #tt (tcode, dcode, dname) values ('t01', 'd1', '四川')
Insert #tt (tcode, dcode, dname) values ('t01', 'd2', '云南')
Insert #tt (tcode, dcode, dname) values ('t01', 'd3', '贵州')
Select
Distinct
tcode,
(Select dcode from #tt Where tcode=A.tcode And dcode='d1') As dcode1,
(Select dname from #tt Where tcode=A.tcode And dcode='d1') As dname1,
(Select dcode from #tt Where tcode=A.tcode And dcode='d2') As dcode2,
(Select dname from #tt Where tcode=A.tcode And dcode='d2') As dname2,
(Select dcode from #tt Where tcode=A.tcode And dcode='d3') As dcode3,
(Select dname from #tt Where tcode=A.tcode And dcode='d3') As dname3
from #tt ASelect * from #tt Adrop table #tt
Insert #tt (tcode, dcode, dname) values ('t01', 'd1', '四川')
Insert #tt (tcode, dcode, dname) values ('t01', 'd2', '云南')
Insert #tt (tcode, dcode, dname) values ('t01', 'd3', '贵州')
Select
Distinct
tcode,
(Select dcode from #tt Where tcode=A.tcode And dcode='d1') As dcode1,
(Select dname from #tt Where tcode=A.tcode And dcode='d1') As dname1,
(Select dcode from #tt Where tcode=A.tcode And dcode='d2') As dcode2,
(Select dname from #tt Where tcode=A.tcode And dcode='d2') As dname2,
(Select dcode from #tt Where tcode=A.tcode And dcode='d3') As dcode3,
(Select dname from #tt Where tcode=A.tcode And dcode='d3') As dname3,
(Select dcode from #tt Where tcode=A.tcode And dcode='d4') As dcode4,
(Select dname from #tt Where tcode=A.tcode And dcode='d4') As dname4,
(Select dcode from #tt Where tcode=A.tcode And dcode='d5') As dcode5,
(Select dname from #tt Where tcode=A.tcode And dcode='d5') As dname5,
(Select dcode from #tt Where tcode=A.tcode And dcode='d6') As dcode6,
(Select dname from #tt Where tcode=A.tcode And dcode='d6') As dname6,
(Select dcode from #tt Where tcode=A.tcode And dcode='d7') As dcode7,
(Select dname from #tt Where tcode=A.tcode And dcode='d7') As dname7from #tt Adrop table #tt
Select
Distinct
tcode,
(Select dcode from TableName Where tcode=A.tcode And dcode='d1') As dcode1,
(Select dname from TableName Where tcode=A.tcode And dcode='d1') As dname1,
(Select dcode from TableName Where tcode=A.tcode And dcode='d2') As dcode2,
(Select dname from TableName Where tcode=A.tcode And dcode='d2') As dname2,
(Select dcode from TableName Where tcode=A.tcode And dcode='d3') As dcode3,
(Select dname from TableName Where tcode=A.tcode And dcode='d3') As dname3,
(Select dcode from TableName Where tcode=A.tcode And dcode='d4') As dcode4,
(Select dname from TableName Where tcode=A.tcode And dcode='d4') As dname4,
(Select dcode from TableName Where tcode=A.tcode And dcode='d5') As dcode5,
(Select dname from TableName Where tcode=A.tcode And dcode='d5') As dname5,
(Select dcode from TableName Where tcode=A.tcode And dcode='d6') As dcode6,
(Select dname from TableName Where tcode=A.tcode And dcode='d6') As dname6,
(Select dcode from TableName Where tcode=A.tcode And dcode='d7') As dcode7,
(Select dname from TableName Where tcode=A.tcode And dcode='d7') As dname7
from TableName A
create table t1(
tcode varchar(10),
dcode varchar(10),
dname varchar(10))insert into t1 select 't01','d1','四川'
insert into t1 select 't01','d2','云南'
insert into t1 select 't01','d3','贵州'
insert into t1 select 't02','d4','湖南'
insert into t1 select 't02','d5','福建'
insert into t1 select 't03','d6','北京'
--执行查询过程
select identity(int,1,1) as id,0 as nid,* into #t from t1
update a set nid = (select count(*) from #t where tcode = a.tcode and id<=a.id) from #t a
declare @nid int
declare @sql varchar(8000)select @nid= max(nid),@sql='' from #twhile @nid>0
begin
set @sql = ',dcode'+rtrim(@nid)+'=max(case when nid='+rtrim(@nid)+' then dcode end)'+
',dname'+rtrim(@nid)+'=max(case when nid='+rtrim(@nid)+' then dname end)'+@sql
set @nid = @nid - 1
endset @sql = 'select tcode'+@sql+' from #t group by tcode order by tcode'exec(@sql)
------------------------------------------------------
t01 d1 四川 d2 云南 d3 贵州
t02 d4 湖南 d5 福建 NULL NULL
t03 d6 北京 NULL NULL NULL NULL
-----------------------------------------------------------
--生成测试数据
create table t1(
tcode varchar(10),
dcode varchar(10),
dname varchar(10))insert into t1 select 't01','d1','四川'
insert into t1 select 't01','d2','云南'
insert into t1 select 't01','d3','贵州'
insert into t1 select 't01','d4','湖南'
insert into t1 select 't01','d6','福建'
insert into t1 select 't02','d1','上海'
insert into t1 select 't02','d3','山东'
insert into t1 select 't02','d4','湖北'
insert into t1 select 't02','d5','广西'
insert into t1 select 't03','d6','北京'
--执行查询过程
declare @nid int
declare @sql varchar(8000)set @sql = ''select @sql = @sql +
',dcode'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+rtrim(dcode)+''' then dcode end)' +
',dname'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+rtrim(dcode)+''' then dname end)'
from t1 group by dcodeset @sql = 'select tcode'+@sql+' from t1 group by tcode order by tcode'exec(@sql)
--查询结果
tcode dcode1 dname1 dcode2 dname2 dcode3 dname3 dcode4 dname4 dcode5 dname5 dcode6 dname6
-----------------------------------------------------------------------------------------------
t01 d1 四川 d2 云南 d3 贵州 d4 湖南 NULL NULL d6 福建
t02 d1 上海 NULL NULL d3 山东 d4 湖北 d5 广西 NULL NULL
t03 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL d6 北京
declare @sql varchar(8000)set @sql = 'select tcode'select @sql = @sql +
' ,dcode'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+
rtrim(dcode)+''' then dcode end)' +
',dname'+right(rtrim(dcode),1)+'=max(case when rtrim(dcode)='''+
rtrim(dcode)+''' then dname end)'
from t1 group by dcodeset @sql = @sql+' from t1 group by tcode order by tcode'exec(@sql)