sql金沙官网线上 行转列

  • ##### 一般在做数据统计的时候会用到行转列,假如要统计学生的成绩,数据库里查询出来的会是这样的,但这并不能达到想要的效果,所以要在查询的时候做一下处理

多行转字符串

这个比较简单,用||或concat函数可以实现

 SQL Code 

1
2

  

select concat(id,username) str from app_user
select id||username str from app_user

          金沙官网线上 1

字符串转多列

实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式

  • CREATE TABLE TestTable(

        [Id] [int] IDENTITY(1,1) NOT NULL,
        [UserName] [nvarchar](50) NULL,
        [Subject] [nvarchar](50) NULL,
        [Source] [numeric](18, 0) NULL
    ) ON [PRIMARY]
    go
    INSERT INTO TestTable ([UserName],[Subject],[Source]) 
        SELECT N'张三',N'语文',60  UNION ALL
        SELECT N'李四',N'数学',70  UNION ALL
        SELECT N'王五',N'英语',80  UNION ALL
        SELECT N'王五',N'数学',75  UNION ALL
        SELECT N'王五',N'语文',57  UNION ALL
        SELECT N'李四',N'语文',80  UNION ALL
        SELECT N'张三',N'英语',100
    GO
    

    这里我用了三种方法来实现行转列第一种:静态行转列

  • select UserName 姓名,

    sum(case Subject when '语文' then Source else 0 end) 语文,sum(case Subject when '数学' then Source else 0 end) 数学,
    sum(case Subject when '英语' then Source else 0 end) 英语 from TestTable group by UserName
    

    用povit行转列

  • select * from

    (select UserName,Subject,Source from TestTable) testpivot(sum(Source) for Subject in(语文,数学,英语)
    ) pvt
    

    用存储过程行转列

  • alter proc pro_test

    @userImages varchar(200),
    @Subject varchar(20),
    @Subject1 varchar(200),
    @TableName varchar(50)
    as
     declare @sql varchar(max)='select * from (select '+@userImages+' from'+@TableName+') tab
    pivot
    (
    sum('+@Subject+') for Subject('+@Subject1+')
    ) pvt'
    exec (@sql)
    go
    exec pro_test 'UserName,Subject,Source',
    'TestTable',
    'Subject',
    '语文,数学,英语'
    

    它们的效果都是这样的

  • 金沙官网线上 2

  • 以上三种方式实现行转列,我们可以根据自己的需求采用不同的方法

字符串转多行

使用union all函数等方式

wm_concat函数

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

 SQL Code 

1
2
3
4
5
6

  

create table test(id number,name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');

效果1 : 行转列 ,默认逗号隔开

 SQL Code 

1

  

select wm_concat(name) name from test;

   

金沙官网线上 3

效果2: 把结果里的逗号替换成"|"

 SQL Code 

1

  

select replace(wm_concat(name),',','|') from test;

   

金沙官网线上 4

效果3: 按ID分组合并name

 SQL Code 

1

  

select id,wm_concat(name) name from test group by id;

   

金沙官网线上 5

sql语句等同于下面的sql语句:

  SQL Code 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

  

-------- 适用范围:8i,9i,10g及以后版本  ( MAX + DECODE )
select id,
       max(decode(rn, 1, name, null)) ||
       max(decode(rn, 2, ',' || name, null)) ||
       max(decode(rn, 3, ',' || name, null)) str
  from (select id,
               name,
               row_number() over(partition by id order by name) as rn
          from test) t
 group by id
 order by 1;
-------- 适用范围:8i,9i,10g及以后版本 ( ROW_NUMBER + LEAD )
select id, str
  from (select id,
               row_number() over(partition by id order by name) as rn,
               name || lead(',' || name, 1) over(partition by id order by name) ||
               lead(',' || name, 2) over(partition by id order by name) || 
               lead(',' || name, 3) over(partition by id order by name) as str
          from test)
 where rn = 1
 order by 1;
-------- 适用范围:10g及以后版本 ( MODEL )
select id, substr(str, 2) str
  from test model return updated rows partition by(id) dimension by(row_number() 
  over(partition by id order by name) as rn) measures(cast(name as varchar2(20)) as str) 
  rules upsert iterate(3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)
  (str [ 0 ] = str [ 0 ] || ',' || str [ iteration_number + 1 ])
 order by 1;
-------- 适用范围:8i,9i,10g及以后版本 ( MAX + DECODE )
select t.id id, max(substr(sys_connect_by_path(t.name, ','), 2)) str
  from (select id, name, row_number() over(partition by id order by name) rn
          from test) t
 start with rn = 1
connect by rn = prior rn + 1
       and id = prior id
 group by t.id;

   

本文由金沙官网线上发布于数据库,转载请注明出处:sql金沙官网线上 行转列

您可能还会对下面的文章感兴趣: