动态行转列(爱新觉罗）

SQL数据聚合技巧

最新推荐文章于 2025-12-05 20:44:21 发布

转载最新推荐文章于 2025-12-05 20:44:21 发布 · 409 阅读

文章标签：

#sql #table #insert

SQLServer 专栏收录该内容

90 篇文章

订阅专栏

源数据表：
      fat1        N01          N02          TXT
---------------------------------------------------------
      AAA          01          211          字符串1
      AAA          01          211          字符串2
      AAA          01          211          字符串3
      AAA          02          212          字符串11
      AAA          02          212          字符串12
      AAA          02          212          字符串13
      BBB          01          310          字符串21
      BBB          01          310          字符串22
      BBB          02          311          字符串31
      BBB          02          311          字符串32
      ..          ..          ..            ...
      ..          ..          ..            ...
      ..          ..          ..            ...

当‘ fat1’‘N01’‘NO2’都具备上下行相等（或只有一条记录）条件时，得出如下结果：

      fat1        N01          N02        TXT      TXT1    TXT2      TXT3(不会超过10个txt字段）
--------------------------------------------------------------------------------------------------
      AAA          01          211      字符串1    字符串2 字符串3
      AAA          02          212      字符串11 字符串12 字符串13
      BBB          01          310      字符串21 字符串22
      BBB          02          311      字符串31 字符串32

      ..          ..          ..            ...
      ..          ..          ..            ...
      ..          ..          ..            ...
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

create table [tb]([fat1] varchar(3),[N01] varchar(2),[N02] int,[TXT] varchar(8))
insert [tb]
select 'AAA','01',211,'字符串1' union all
select 'AAA','01',211,'字符串2' union all
select 'AAA','01',211,'字符串3' union all
select 'AAA','02',212,'字符串11' union all
select 'AAA','02',212,'字符串12' union all
select 'AAA','02',212,'字符串13' union all
select 'BBB','01',310,'字符串21' union all
select 'BBB','01',310,'字符串22' union all
select 'BBB','02',311,'字符串31' union all
select 'BBB','02',311,'字符串32'
go

--SQL SERVER 2000 动态SQL,指数量不定。
declare @sql varchar(8000)
set @sql = 'select fat1,N01,N02 '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then txt else '''' end) [txt' + cast(px as varchar) + ']'
from (select distinct px from (select *,px = (select count(1) from tb where fat1=t.fat1 and N01=t.N01 and N02 = t.N02 and txt < t.txt) + 1 from tb t) m) as a
set @sql = @sql + ' from (select *,px = (select count(1) from tb where fat1=t.fat1 and N01=t.N01 and N02 = t.N02 and txt < t.txt) + 1 from tb t) m group by fat1,N01,N02'
exec(@sql)

drop table tb

/*
fat1 N01 N02         txt1     txt2     txt3
---- ---- ----------- -------- -------- --------
AAA 01   211         字符串1     字符串2     字符串3
AAA 02   212         字符串11    字符串12    字符串13
BBB 01   310         字符串21    字符串22
BBB 02   311         字符串31    字符串32

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/htl258/archive/2009/03/22/4015646.aspx