精华内容
下载资源
问答
  • db2数据库中的行转列和列转行

    千次阅读 2020-04-21 20:30:12
    一、行转列 给出下面数据: CREATE TABLE Sales (Year INT, Quarter INT, Results INT) YEAR QUARTER RESULTS ----------- ----------- ----------- 2004 1 20 2004 ...

    一、行转列


    给出下面的数据:
    CREATE TABLE Sales (Year INT, Quarter INT, Results INT)

    YEAR        QUARTER     RESULTS
    ----------- ----------- -----------
    2004           1          20
    2004           2          30
    2004           3          15
    2004           4          10
    2005           1          18
    2005           2          40
    2005           3          12
    2005           4          27
    想要的到结果:
    YEAR        Q1          Q2          Q3          Q4
    ----------- ----------- ----------- ----------- -----------
    2004          20          30          15          10
    2005          18          40          12          27
    这个SQL就可解决这个问题:
    SELECT Year,
           MAX(CASE WHEN Quarter = 1
               THEN Results END) AS Q1,
           MAX(CASE WHEN Quarter = 2
               THEN Results END) AS Q2,
           MAX(CASE WHEN Quarter = 3
               THEN Results END) AS Q3,
           MAX(CASE WHEN Quarter = 4
               THEN Results END) AS Q4
    FROM Sales
    GROUP BY Year
    解释一下为什么要加max的原因,因为不加max的话结果会是这样:
    YEAR        Q1          Q2          Q3          Q4
    ----------- ----------- ----------- ----------- -----------
    2004          20           -           -           -
    2004           -          30           -           -
    2004           -           -          15           -
    2004           -           -           -          10
    2005          18           -           -           -
    2005           -          40           -           -
    2005           -           -          12           -
    2005           -           -           -          27

     

    二、列转行

    给出下面数据

    CREATE TABLE SalesAgg
    (  year INTEGER,
       q1 INTEGER,
       q2 INTEGER,
       q3 INTEGER,
       q4 INTEGER );
    
    YEAR        Q1          Q2          Q3          Q4
    ----------- ----------- ----------- ----------- -----------
    2004          20          30          15          10
    2005          18          40          12          27
    
    想要的结果
    YEAR        QUARTER     RESULTS
    ----------- ----------- -----------
    2004           1          20
    2004           2          30
    2004           3          15
    2004           4          10
    2005           1          18
    2005           2          40
    2005           3          12
    2005           4          27

    这个SQL就可以实现:

    SELECT S.Year, Q.Quarter, Q.Results
    FROM SalesAgg AS S,
         TABLE (VALUES(1, S.q1),
                      (2, S.q2),
                      (3, S.q3),
                      (4, S.q4))
                AS Q(Quarter, Results);

     每个values中对应列的数据类型必须相同,值可以任意,如1,2,3,4都是整形

    下面解释一下执行的过程:
    核心是用table函数创建了一个表,Q是创建的视图名,这个表是用values实现的多行表,values实现虚表的例子:

    db2 => select * from (values (1,2),(2,3)) as t1(col1,col2)
    
    COL1        COL2
    ----------- -----------
     1           2
     2           3
    
      2 条记录已选择。

    db2 => select * from (values 1) as a

    1
    -----------
              1

    1 条记录已选择。
     

    所不同的是这里跟from子句中的一个表产生了关系,取出了表中的一列作为数据.

    展开全文
  • 数据库查询中的行转列,在项目开发中用的很多,尤其一些财务报表,本章我们来讨论一下行转列。什么是行转列数据的原始数据:学号 姓名 课程 成绩、001 张三 语文 60001 张三 数学 89001 张三 英语 88002 李四 语文 ...

    175dd792256b6f7c19a8b60cc889e94e.png

    数据库查询中的行转列,在项目开发中用的很多,尤其一些财务报表,本章我们来讨论一下行转列。

    什么是行转列

    数据的原始数据:

    学号 姓名 课程 成绩、

    001 张三 语文 60

    001 张三 数学 89

    001 张三 英语 88

    002 李四 语文 88

    002 李四 数学 66

    002 李四 英语 90

    转换后的效果:

    学号     姓名 语文 数学 英语

    001     张三  60    89    88

    002     李四  88    66    90

    行转列需要的技术

    1)连接查询

    使用inner join或left join将多表连接在一起

    2)分组查询

    使用group by子句对特定列进行分组,如上面案例中的学号

    3)聚合函数

    使用max函数取得有值的数据

    4)CASE语句

    想CASE语句可以嵌入SQL语句中,用于条件判断语法:CASE    WHEN 条件 THEN 结果    WHEN 条件 THEN 结果    WHEN 条件 THEN 结果    ELSE 结果ENDCASE 列    WHEN 值 THEN 结果    WHEN 值 THEN 结果    WHEN 值 THEN 结果    ELSE 结果END

    行转列的实现

    表结构:

    drop table if exists student;create table student(stu_id int primary key auto_increment,stu_name varchar(20),stu_gender varchar(20),stu_age int);drop table if exists course;create table course(course_id int primary key auto_increment,course_name varchar(20));drop table if exists score;create table score(score_id int primary key auto_increment,stu_id int,course_id int,score int,constraint fk_stu_id foreign key (stu_id) references student(stu_id),constraint fk_course_id foreign key (course_id) references course(course_id));insert into student(stu_name,stu_gender,stu_age)values('张三','男',15),('李四','男',15),('王五','男',15),('赵六','男',15);insert into course(course_name)values('语文'),('数学'),('英语');insert into score(stu_id,course_id,score)values(1,1,80),(1,2,82),(1,3,84),(2,1,60),(2,2,70),(2,3,86),(3,1,83),(3,2,77),(3,3,89);
    1. 通过左连接查询将所有表连接起来

    select s.stu_id '学号',s.stu_name '姓名',c.course_name '课程',sc.score '分数'from student s left join score sc on s.stu_id = sc.stu_idleft join course c on c.course_id = sc.course_id;

    结果:

    学号 姓名   语文 数学 英语

    2 小周 语文 60

    2 小周 数学 70

    2 小周 英语 86

    1 小张 语文 80

    1 小张 数学 82

    1 小张 英语 84

    3 王五 语文 83

    3 王五 数学 77

    3 王五 英语 89

    4 赵六

    1. 使用case语句将每门课转换为列

    select s.stu_id '学号',s.stu_name '姓名',-- 判断课程名称如果是语文,就把语文成绩作为列的值,否则值为0case c.course_name when '语文' then sc.score else 0 end '语文',case c.course_name when '数学' then sc.score else 0 end '数学',case c.course_name when '英语' then sc.score else 0 end '英语'from student s left join score sc on s.stu_id = sc.stu_idleft join course c on c.course_id = sc.course_id;查询结果:学号 姓名   语文 数学 英语2       小周      60      0           02       小周      0      70      02       小周      0      0           861       小张      80      0           01       小张      0      82      01       小张      0         0          843       王五      83    0           03       王五      0         77        03       王五      0         0           894       赵六      0         0           0
    1. 按学号分组

    select s.stu_id '学号',s.stu_name '姓名',(case c.course_name when '语文' then sc.score else 0 end) '语文',(case c.course_name when '数学' then sc.score else 0 end) '数学',(case c.course_name when '英语' then sc.score else 0 end) '英语'from student s left join score sc on s.stu_id = sc.stu_idleft join course c on c.course_id = sc.course_idgroup by s.stu_id;查询结果:学号 姓名   语文 数学 英语1       小张      80      0           02       小周      60      0           03       王五      83      0           04       赵六      0       0           0
    1. 使用Max和Sum统计分数获得每门课大于0的分数

    select s.stu_id '学号',s.stu_name '姓名',max(case c.course_name when '语文' then sc.score else 0 end) '语文',max(case c.course_name when '数学' then sc.score else 0 end) '数学',max(case c.course_name when '英语' then sc.score else 0 end) '英语'from student s left join score sc on s.stu_id = sc.stu_idleft join course c on c.course_id = sc.course_idgroup by s.stu_id;查询结果:学号 姓名   语文 数学 英语1       小张      80      82      842       小周      60      70      863       王五      83      77      894       赵六      0       0       0

    以上就是一种常用的行转列实现方式,希望对大家有帮助。

    展开全文
  • select * from (select name, nums as "Purchase Frequency"from demo t)pivot xml (sum(nums) for name in (any))如你所见, NAME_XML ...你可以使用任何 XML 分析器中的输出生成更有用的输出结论Pivot 为 SQL 语...

    select * from (

    select name, nums as "Purchase Frequency"

    from demo t

    )

    pivot xml (

    sum(nums) for name in (any)

    )

    3ee8686bed1d87cf5ff9bae6176a40be.png

    如你所见,列 NAME_XML 是 XMLTYPE,其中根元素是 。每个值以名称-值元素对的形式表示。你可以使用任何 XML 分析器中的输出生成更有用的输出

    8c7a9cb344a508e89bf887aff72fc8b6.png

    结论

    Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表,而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样,您可以使用 unpivot 操作转换任何交叉表报表,以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出,您不必指定 pivot 操作需要搜索的值域。

    在这附上pivot  in 多列,不固定列的动态实现方法

    CREATE OR REPLACE PROCEDURE PIVOT_TEST IS

    V_SQL VARCHAR2(2000);

    CURSOR CURSOR_1 IS SELECT  WM_CONCAT(''''|| ( SJWZ)||''''||'as '|| sjwz) SJWZ from (select distinct SJWZ from f_wz_lbmc where ckbh=2);

    BEGIN

    V_SQL := 'select * from (select sum(a.jhje) 金额 ,c.lbmc 类别名称 ,e.ksmc 科室名称,e.sjks 上级科室

    from p_wz_ly02 a,d_wz_wpfl b,d_wz_lbmc c,p_wz_ly01 d,d_gy_ksdm e

    where  a.wpbh=b.wpbh

    and  b.lbbh=c.lbbh

    and  a.djbh=d.djbh

    and  d.ksdm=e.ksdm

    and  d.ckbh= 2

    group by c.lbmc , e.ksmc,e.sjks

    ) pivot(sum(金额) for 类别名称 IN  (';

    FOR V_SJWZ IN CURSOR_1

    LOOP

    V_SQL := V_SQL ||V_SJWZ.SJWZ

    ;

    END LOOP;

    V_SQL := V_SQL || '))  ORDER BY 上级科室';

    --DBMS_OUTPUT.PUT_LINE(V_SQL);

    V_SQL := 'CREATE OR REPLACE VIEW PIVOT_TEST11  AS '||  V_SQL;

    --DBMS_OUTPUT.PUT_LINE(V_SQL);

    EXECUTE IMMEDIATE V_SQL;

    END;

    注意有时候会报权限不足错误,需要使用dba用户授权

    Grant create any view to user;

    还有一种在命令行(或者pl/sql的命令窗口)中实现的

    clear columns

    COLUMN temp_in_statement new_value str_in_statement

    SQL> SELECT  WM_CONCAT(''''||SJWZ||''''||'as '|| sjwz) as temp_in_statement from f_wz_lbmc where ckbh=1;

    TEMP_IN_STATEMENT

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

    '燃料油料','办公用品','五金电料','房屋修缮','卫生用品','印刷品','被服材料','专项物质','其他'

    SQL> select * from (select sum(a.jhje) 金额 ,c.lbmc 类别名称 ,e.ksmc 科室名称,e.sjks 上级科室

    2      from p_wz_ly02 a,d_wz_wpfl b,d_wz_lbmc c,p_wz_ly01 d,d_gy_ksdm e

    3         where  a.wpbh=b.wpbh

    4           and  b.lbbh=c.lbbh

    5           and  a.djbh=d.djbh

    下面是几个字符串转换函数

    多行转字符串

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

    SQL

    select concat(id,username) str from app_user ;

    select id||username str from app_user;

    字符串转多列

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

    字符串转多行

    使用union all函数等方式

    listagg函数

    SQL

    select listagg(ename,' , ')  within group (order by ename) from emp;

    语法: listagg(列名,分隔符) within group (order by 列名)

    4、需要注意的事项如下:

    (1). 必须得分组,也就是说group  by是必须的。

    (2). listagg函数的第一个参数是需要显示的字段,也就是log_name;第二个参数是数值之间的分隔符,可以是任意字符;同时还需要进行排序和分组within group (order by name)

    wm_concat函数

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

    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

    select wm_concat(name) name from test;

    95f6cb3670828efcdd02ca0ca4db0489.png

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

    SQL

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

    5c27b38493da9b9cd3eee4f07110e7b0.png

    效果3:按ID分组合并name

    SQL

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

    b1d4af66e717a872ed76fcb48be65adc.png

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

    SQL

    -------- 适用范围: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;

    懒人扩展用法:

    案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单,假设我的APP_USER表中有(id,username,password,age)4个字段。查询结果如下

    SQL

    /** 这里的表名默认区分大小写 */

    select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sqlStr

    from user_tab_columns where table_name='APP_USER';

    8525cb347080d3a30e91972ad6db3938.png

    利用系统表方式查询

    SQL

    select * from user_tab_columns

    bebc29030c5d22b619039c2b5d3c1ac6.png

    展开全文
  • 行转列,列转行是我们在开发过程经常碰到问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 2005 新增运算符PIVOT来实现。 用传统方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、...
    行转列,列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。 用传统的方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。

     

    我们首先先通过一个老生常谈的例子,学生成绩表(下面简化了些)来形象了解下行转列

     CREATE  TABLE [StudentScores]
    (
       
    [UserName]         NVARCHAR(20),        --学生姓名
        [Subject]          NVARCHAR(30),        --科目
        [Score]            FLOAT,               --成绩
    )

    INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80

    INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90

    INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70

    INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85

    INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80

    INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90

    INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70

    INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85




    如果我想知道每位学生的每科成绩,而且每个学生的全部成绩排成一行,这样方便我查看、统计,导出数据


    SELECT
          UserName,
         
    MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
         
    MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
         
    MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
         
    MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
    FROM dbo.[StudentScores]
    GROUP BY UserName




    查询结果如图所示,这样我们就能很清楚的了解每位学生所有的成绩了


     

     

    接下来我们来看看第二个小列子。有一个游戏玩家充值表(仅仅为了说明,举的一个小例子),

     


    Code [http://www.oeedu.com]


    CREATE TABLE [Inpours] ( [ID] INT IDENTITY(1,1), [UserName] NVARCHAR(20), --游戏玩家 [CreateTime] DATETIME, --充值时间 [PayType] NVARCHAR(20), --充值类型 [Money] DECIMAL, --充值金额 [IsSuccess] BIT, --是否成功 1表示成功, 0表示失败 CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID) ) INSERT INTO Inpours SELECT '张三', '2010-05-01', '支付', 50, 1 INSERT INTO Inpours SELECT '张三', '2010-06-14', '支付宝', 50, 1 INSERT INTO Inpours SELECT '张三', '2010-06-14', '手机短信', 100, 1 INSERT INTO Inpours SELECT '李四', '2010-06-14', '手机短信', 100, 1 INSERT INTO Inpours SELECT '李四', '2010-07-14', '支付宝', 100, 1 INSERT INTO Inpours SELECT '王五', '2010-07-14', '工商银行卡', 100, 1 INSERT INTO Inpours SELECT '赵六', '2010-07-14', '建设银行卡', 100, 1


     

    下面来了一个统计数据的需求,要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

     



    Code [http://www.oeedu.com]


    SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, CASE PayType WHEN '支付宝' THEN SUM(Money) ELSE 0 END AS '支付宝', CASE PayType WHEN '手机短信' THEN SUM(Money) ELSE 0 END AS '手机短信', CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡', CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡' FROM Inpours GROUP BY CreateTime, PayType



    如图所示,我们这样只是得到了这样的输出结果,还需进一步处理,才能得到想要的结果


     





    SELECT
           CreateTime,
          
    ISNULL(SUM([支付宝]), 0) AS [支付宝],
          
    ISNULL(SUM([手机短信]), 0) AS [手机短信],
          
    ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡],
          
    ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡]
    FROM
    (
       
    SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
              
    CASE PayType WHEN '支付宝'     THEN SUM(Money) ELSE 0 END AS '支付宝',
              
    CASE PayType WHEN '手机短信'   THEN SUM(Money) ELSE 0 END AS '手机短信',
              
    CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡',
              
    CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡'
       
    FROM Inpours
       
    GROUP BY CreateTime, PayType
    ) T
    GROUP BY CreateTime



    其实行转列,关键是要理清逻辑,而且对分组(Group by)概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了,上面是我为了说明弄的一个简单列子。实际中,可能支付方式特别多,而且逻辑也复杂很多,可能涉及汇率、手续费等等(曾经做个这样一个),如果支付方式特别多,我们的CASE WHEN 会弄出一大堆,确实比较恼火,而且新增一种支付方式,我们还得修改脚本如果把上面的脚本用动态SQL改写一下,我们就能轻松解决这个问题

     


    Code [http://www.oeedu.com]


    DECLARE @cmdText VARCHAR(8000); DECLARE @tmpSql VARCHAR(8000); SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' CHAR(10); SELECT @cmdText = @cmdText ' CASE PayType WHEN ''' PayType ''' THEN SUM(Money) ELSE 0 END AS ''' PayType ''',' CHAR(10) FROM (SELECT DISTINCT PayType FROM Inpours ) T SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里,如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1) SET @cmdText = @cmdText ' FROM Inpours GROUP BY CreateTime, PayType '; SET @tmpSql ='SELECT CreateTime,' CHAR(10); SELECT @tmpSql = @tmpSql ' ISNULL(SUM(' PayType '), 0) AS ''' PayType ''',' CHAR(10) FROM (SELECT DISTINCT PayType FROM Inpours ) T SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) ' FROM (' CHAR(10); SET @cmdText = @tmpSql @cmdText ') T GROUP BY CreateTime '; PRINT @cmdText EXECUTE (@cmdText);



    下面是通过PIVOT来进行行转列的用法,大家可以对比一下,确实要简单、更具可读性(呵呵,习惯的前提下)



    Code [http://www.oeedu.com]


    SELECT CreateTime, [支付宝] , [手机短信], [工商银行卡] , [建设银行卡] FROM ( SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money FROM Inpours ) P PIVOT ( SUM(Money) FOR PayType IN ([支付宝], [手机短信], [工商银行卡], [建设银行卡]) ) AS T ORDER BY CreateTime



     

    有时可能会出现这样的错误:

     

    消息 325,级别 15,状态 1,第 9 行

     

    'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值,以启用此功能。有关存储过程 sp_dbcmptlevel 的信息,请参见帮助。

     

    这个是因为:对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息,请参阅 sp_dbcmptlevel (Transact-SQL)。 例如,只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了, Test 是所在数据库的名称。

     


     

    下面我们来看看列转行,主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表

     

    代码

    CREATE TABLE ProgrectDetail
    (
    ProgrectName
    NVARCHAR(20), --工程名称
    OverseaSupply INT, --海外供应商供给数量
    NativeSupply INT, --国内供应商供给数量
    SouthSupply INT, --南方供应商供给数量
    NorthSupply INT --北方供应商供给数量
    )

    INSERT INTO ProgrectDetail
    SELECT 'A', 100, 200, 50, 50
    UNION ALL
    SELECT 'B', 200, 300, 150, 150
    UNION ALL
    SELECT 'C', 159, 400, 20, 320
    UNION ALL
    SELECT 'D', 250, 30, 15, 15


     

     

     

    我们可以通过下面的脚本来实现,查询结果如下图所示

     

    代码

    SELECT ProgrectName, 'OverseaSupply' AS Supplier,
    MAX(OverseaSupply) AS 'SupplyNum'
    FROM ProgrectDetail
    GROUP BY ProgrectName
    UNION ALL
    SELECT ProgrectName, 'NativeSupply' AS Supplier,
    MAX(NativeSupply) AS 'SupplyNum'
    FROM ProgrectDetail
    GROUP BY ProgrectName
    UNION ALL
    SELECT ProgrectName, 'SouthSupply' AS Supplier,
    MAX(SouthSupply) AS 'SupplyNum'
    FROM ProgrectDetail
    GROUP BY ProgrectName
    UNION ALL
    SELECT ProgrectName, 'NorthSupply' AS Supplier,
    MAX(NorthSupply) AS 'SupplyNum'
    FROM ProgrectDetail
    GROUP BY ProgrectName


     

     

     

     

    UNPIVOT 实现如下:

     

    代码

    SELECT ProgrectName,Supplier,SupplyNum
    FROM
    (
    SELECT ProgrectName, OverseaSupply, NativeSupply,
    SouthSupply, NorthSupply
    FROM ProgrectDetail
    )T
    UNPIVOT
    (
    SupplyNum
    FOR Supplier IN
    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
    ) P


    展开全文
  • PIVOT通过将表达式某一列中的唯一值转换为输出...通俗简单的说:PIVOT就是行转列,UNPIVOT就是列传行 一、PIVOT实例 1. 建表 建立一个销售情况表,其中,year字段表示年份,quarter字段表示季度,amount字段表...
  • 行转列,列转行是我们在开发过程经常碰到问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 2005 新增运算符PIVOT来实现。 用传统方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、...
  • pivot函数:行转列函数:语法:pivot(任一聚合函数 for 需专列值所在列名 in (需转为列名值));unpivot函数:列转行函数:语法:unpivot(新增值所在列列名 for 新增列转为行后所在列列名 in (需转为行列名...
  • 最近在开发过程中遇到问题,需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询字段进行显示。借此机会,在网上查阅了相关方法,现总结出一种比较简单易懂方法备用。一、...
  • 最近负责模块设计到统计,由于前期设计思路比较清晰,在统计这块,只需要从数据库中统计出相关数据库就可以了。 统计表:表头:文件类型、(所选年份)1-12月份 但是直接出去来数据,无法直接在页面上展示:...
  • 1、行转列所谓行转列,即将一行数据转成多行显示,或者说将多列数据转...有些数据库行转列的专用函数,具体可查询相关文档。select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from ...
  • 平时工作我们经常遇到这样问题,我们在创建数据表时候是根据业务实际情况设计,但随着业务拓展和需求变化以前我们是通过横向去使用、分析这张表,后来慢慢从纵向去分析了,这里我举个例子就明白了。...
  • 最近在开发过程中遇到问题,需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询字段进行显示。 借此机会,在网上查阅了相关方法,现总结出一种比较简单易懂方法备用。 ...
  • 1)查询员工姓名包含字符A员工信息 2)查询员工姓名第二个字母为A员工信息 分组 select 查询数据 from 数据来源 where 过滤条件 group by 分组字段1,分组字段2… having 组过滤条件 order by 排序字段…; ...
  • 开发过程中常遇到行转列或是列转行问题,即需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询字段进行显示。一、行转列:将原来同一列下多行不同内容作为多个字段,输出...
  • 本帖子是行转列的一个逆向操作——列转行,看下面一个面试题面试题2: 柠檬班第30期学生要毕业了,他们Linux、MySQL、Java成绩数据表 tb_lemon_grade_column, 表字段student_name,Linux,MySQL,Java分别...
  • 行转列及列转行查询

    2020-12-30 17:18:16
    开发过程中常遇到行转列或是列转行问题,即需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询字段进行显示。 一、行转列:将原来同一列下多行不同内容作为多个字段,...
  • 以前做oracle开发时会想到使用行转列的方法来做,突然间感觉那个方法有点麻烦,所以换种思路来解决问题,下面模拟一个应用场景: 学生表存放 学生姓名、语文成绩、数学成绩、英语成绩,查询出每个学生最高成绩...
  • 利用JAVA程序,对数据库中某些列、某些行的数据导出到已有PDF模板中。附有WORD,WORD中已有表模型;附有由WORD表转换PDF模板;附有生成后PDF文件。代码中附有注释,简介易懂。主要利用ITEXT插件。
  • PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个来旋转表值表达式,并在必要时对最终输出中所需的任何其余值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的转换为值。 注意 对...
  • 本文介绍实例成功实现了动态行转列。下面我以一个简单的数据库为例子,说明一下。 数据表结构 这里我用一个比较简单例子来说明,也是行转列的经典例子,就是学生成绩 三张表:学生表、课程表、成绩表 学生表...
  • 在SQLSERVER时发现for xml path写法为行转列,可以使用wm_concat函数进行替换,但是wm_concat函数默认用逗号分割,所以使用不方便,可以使用LISTAGG函数进行替换。wm_concat、LISTAGG均与ORACLE兼容。 LISTAGG...
  • 在我们使用的数据库经常需要用到行列互相转换情况,使用sql 关键词UNPIVOT(列转行)和PIVOT(行转列)可轻松实现行列转换。一、列转行:员工月份排班表存储是采用1号~31号作为列方式进行存储现通过UNPIVOT ...
  • 数据库操作,有些时候我们遇到需要实现“行转列需求,例如一下表为某店铺一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据: INSERT INTO WEEK_INCOME ...
  • 我们经过深思熟虑在数据库中设计数据表,最终还是不能适应所有业务场景。包括在面试过程中会遇到这么一个SQL像我一样菜人来讲,还是有点难度,所以今天把这个问题给做一下笔记。我们创建一个成绩表,在成绩...
  • 九月份复习,十月份考试,十月底一直没法收心,赶在十一初 由于不可抗拒的原因又不得不重新找工作就;...这次就简单介绍下oracle数据库下如何实现行转列、列转行及此在mybatis中的实现方式,就具体用法我就不详细说...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 553
精华内容 221
关键字:

数据库中的行转列、