特殊的交叉表

问:

有一个表有几个字段如下
jgid  capitalnumber tr_type
9452  351101314351  tru
9452  351101314352  stru
9452  351101314353  tru
9452  351101314354  tru
9452  351101314356  stru
9452  351101314358  tru

想成生这样的表
jgid  capitalnumber1 tr_type1 capitalnumber2 tr_type2  ... ... capitalnumber6 tr_type6
9452  351101314351   tru      351101314352   stru      ... ... 351101314358   tru

就是同样的jgid值连续出现的次数最多为6个，有的jgid可能只有1个到5个，都把它们拉成横向显示的,jgid 这个字段只留一条,横向它的其它字段显示最多为6个，不足6个的留空

这个用游标和不用游标分别怎么做呢，我这个表有9千多行

----------------------------------------------------------------------------------

第一种答案:

if object_id('tbTest') is not null
    drop table tbTest
GO
----创建测试数据
create table tbTest(jgid int,capitalnumber varchar(20),tr_type varchar(10))
insert tbTest
select 9452,  '351101314351',  'tru' union all
select 9452,  '351101314352',  'stru' union all
select 9452,  '351101314353',  'tru' union all
select 9453,  '351101314358',  'tru' union all-----------
select 9452,  '351101314354',  'tru' union all
select 9452,  '351101314356',  'stru' union all
select 9452,  '351101314358',  'tru'

----行转列

select jgid, capitalnumber, tr_type, 0 ct_id into #t from tbtest order by jgid

declare @i int
declare @t int
set @i = 1
set @t = -1
update #t
set @i = case when @t = jgid then @i + 1 else 1 end, @t = jgid, ct_id = @i

select jgid,
max(case ct_id when 1 then capitalnumber else '' end) as capitalnumber1,
max(case ct_id when 1 then tr_type else '' end) as tr_type1,
max(case ct_id when 2 then capitalnumber else '' end) as capitalnumber2,
max(case ct_id when 2 then tr_type else '' end) as tr_type2,
max(case ct_id when 3 then capitalnumber else '' end) as capitalnumber3,
max(case ct_id when 3 then tr_type else '' end) as tr_type3,
max(case ct_id when 4 then capitalnumber else '' end) as capitalnumber4,
max(case ct_id when 4 then tr_type else '' end) as tr_type4,
max(case ct_id when 5 then capitalnumber else '' end) as capitalnumber5,
max(case ct_id when 5 then tr_type else '' end) as tr_type5,
max(case ct_id when 6 then capitalnumber else '' end) as capitalnumber6,
max(case ct_id when 6 then tr_type else '' end) as tr_type6
from #t
group by jgid
----清除测试环境
drop table #t
drop table tbTest

----------------------------------------------------------------------------------

第二种答案:

USE MASTER      /*在MASTER数据库中进行测试*/
if object_id('tbTest') is not null
    drop table tbTest
if object_id('spCrossTotal') is not null
    drop proc spCrossTotal
if object_id('viewTest') is not null
    drop view viewTest
GO
----创建测试数据
create table tbTest(jgid int,capitalnumber varchar(20),tr_type varchar(10))
insert tbTest
select 9452,  '351101314351',  'tru' union all
select 9452,  '351101314352',  'stru' union all
select 9452,  '351101314353',  'tru' union all
select 9452,  '351101314354',  'tru' union all
select 9452,  '351101314356',  'stru' union all
select 9452,  '351101314358',  'tru' union all
select 9453,  '351101314359',  'tru'    /*此行为新插入测试行*/
GO
----创建行转列的存储过程
create proc spCrossTotal
as
  --创建用于行转列的临时表
  select *,0 as GroupID into #tmp from tbTest
  --生成行转列分组列
  declare @jgid int,@GroupId int
  set @jgid = 0
  set @GroupId = 1
  update #tmp set
  @GroupId = case when @jgid = jgid then @GroupId + 1 else 1 end,
  @jgid = jgid,GroupID = @GroupID
  --生成通用动态行转列的SQL字符串
  declare @sql varchar(8000)
  set @sql = ''
  SELECT @sql = @sql + ',capitalnumber' + rtrim(GroupId) + '= max(case GroupId when ' + rtrim(GroupId) + ' then capitalnumber end),
  tr_type' + rtrim(GroupId) + '= max(case GroupId when ' + rtrim(GroupId) + ' then tr_type end)'
  FROM #tmp GROUP BY GroupId
  set @sql = 'select jgid' + @sql + ' from #tmp group by jgid'
  --执行行转列SQL语句
  EXEC(@sql)
GO

----创建行转列视图,对行转列存储过程进行封装
create view viewTest
as
select * from openrowset('sqloledb','trusted_connection=yes','SET FMTONLY OFF;SET NOCOUNT ON ;EXEC master..spCrossTotal')
GO

----执行视图
select * from viewTest

----清除测试环境
drop table tbTest
drop proc spCrossTotal
drop view viewTest

/*结果
jgid  capitalnumber1 tr_type1 capitalnumber2 tr_type2  ... capitalnumber6 tr_type6
----------------------------------------------------------------------------------
9452  351101314351   tru      351101314352   stru      ... 351101314358   tru
9453  351101314359   tru      NULL           NULL      ... NULL           NULL
*/

----------------------------------------------------------------------------------

第三种答案:

--我加了一行数据
--insert into tab1(jgid ,capitalnumber, tr_type) values('9453','351101319451','tru')

if object_id('pubs..tab1') is not null
   drop table tab1
go
if object_id('pubs..tab2') is not null
   drop table tab2
go

create table tab1
(
jgid varchar(10),
capitalnumber varchar(20),
tr_type varchar(10)
)
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314351','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314352','stru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314353','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314354','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314356','stru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314358','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9453','351101319451','tru')

select * , px=(select count(1) from tab1 where jgid=a.jgid and capitalnumber<a.capitalnumber)+1 into tab2 from tab1 a
order by jgid , capitalnumber

declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ', max(case when px =  ''' + rtrim(cast(px as varchar(10))) + ''' then capitalnumber else null end) as capitalnumber' + rtrim(cast(px as varchar(10))) + ',
                        max(case when px =  ''' + rtrim(cast(px as varchar(10))) + ''' then tr_type       else null end) as tr_type'       + rtrim(cast(px as varchar(10)))
from tab2 group by px order by px
set @sql = 'select jgid ' + @sql + ' from tab2 group by jgid'

EXEC(@sql)

drop table tab1
drop table tab2

************************************************************************************

我的结论:这里面前两种方法其实是一种方法，第二种要求把题目写成视图，这两种方法有两句很精典

declare @i int
declare @t int
set @i = 1
set @t = -1
update #t
set @i = case when @t = jgid then @i + 1 else 1 end, @t = jgid, ct_id = @i

declare @jgid int,@GroupId int
set @jgid = 0
set @GroupId = 1
update #tmp set
@GroupId = case when @jgid = jgid then @GroupId + 1 else 1 end,
@jgid = jgid,GroupID = @GroupID

上面这两组sql语句很经典,他们的方法是相同，一直不知道update 语句是可以作循环的,以后多研究一下

----创建行转列视图,对行转列存储过程进行封装
create view viewTest
as
select * from openrowset('sqloledb','trusted_connection=yes','SET FMTONLY OFF;SET NOCOUNT ON ;EXEC master..spCrossTotal')
GO

上面这个视图其实是调用了存储过程，这种方法是首次见到，以后会派上用场的