精华内容
下载资源
问答
  • mssql 数据库行转列,列转行终极方案,需要的朋友可以参考下。
  • 数据库行转列

    2008-08-01 00:38:31
    数据库里的,表里的行转列
  • 数据库行转列与列转行方法

    万次阅读 2018-06-04 23:51:28
    插入数据 INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '语文', 60); INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '数学', 70); INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) ...

    --创建纵表TABLE_A

    CREATE TABLE TABLE_A (
        NAME VARCHAR2(20),
        SUBJECT VARCHAR2(20),
        SCORE NUMBER(22,0));
    --向表TABLE_A中插入数据
    INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '语文', 60);
    INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '数学', 70);
    INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '英语', 80);
    INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '语文', 90);
    INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '数学', 100);
    插入数据后TABLE_A如下图:
    --创建纵表TABLE_B
    CREATE TABLE TABLE_B (
        NAME VARCHAR2(20),
        CHINESE NUMBER(22,0),
        MATH NUMBER(22,0),
        ENGLISH NUMBER(22,0));

    --向表TABLE_B中插入数据
    INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('张三', 60, 70, 80);
    INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('李四', 90, 100, 0);
    插入数据后 TABLE_B如下图:
    --纵表转横表 TABLE_A-->TABLE_B

    --方法一:使用case ... when ... then ... else ... end  函数
    select name,
            sum (case subject when '语文' then score else 0 end) as chinese,
            sum (case subject when '数学' then score else 0 end) as math,
            sum (case subject when '英语' then score else 0 end) as english
            from Table_A
            group by name

    --方法二:使用pivot
    select * from Table_A pivot (max(score)for subject in('语文','数学','英语'))

    --横表转纵表 TABLE_B-->TABLE_A
    --方法一:使用union all
    select NAME,'语文' as SUBject,chinese as score from Table_B union all
    select NAME,'数学' as SUBject,math as score from Table_B union all
    select NAME,'英语' as SUBject,english as score from Table_B
    order by name,subject desc
    --方法二:使用unpivot

    select name,subject,score from Table_B
    unpivot
    (score for subject in (chinese,math,english))


     

    展开全文
  • 数据库 行转列 列转行详解

    万次阅读 2012-04-04 22:54:57
    行转列 列转行 [一]、行转列   1.1、初始测试数据   表结构:TEST_TB_GRADE   create table TEST_TB_GRADE ( ID NUMBER(10) not null, USER_NAME VARCHAR2(20 CHAR), COURSE VARCHAR2

    目录结构如下:

    • 行转列
    • 列转行

    [一]、行转列

     

    1.1、初始测试数据

     

    表结构:TEST_TB_GRADE

     

    create table TEST_TB_GRADE   
    1. (   
    2.   ID        NUMBER(10) not null,   
    3.   USER_NAME VARCHAR2(20 CHAR),   
    4.   COURSE    VARCHAR2(20 CHAR),   
    5.   SCORE     FLOAT  
    6. )  
    create table TEST_TB_GRADE
    (
      ID        NUMBER(10) not null,
      USER_NAME VARCHAR2(20 CHAR),
      COURSE    VARCHAR2(20 CHAR),
      SCORE     FLOAT
    )

     初始数据如下图:


                           

     

    1.2、 如果需要实现如下的查询效果图:


                        

     

    这就是最常见的行转列,主要原理是利用decode函数、聚集函数(sum),结合group by分组实现的,具体的sql如下:

    select t.user_name,   
    1.   sum(decode(t.course, '语文', score,null)) as CHINESE,   
    2.   sum(decode(t.course, '数学', score,null)) as MATH,   
    3.   sum(decode(t.course, '英语', score,null)) as ENGLISH   
    4. from test_tb_grade t   
    5. group by t.user_name   
    6. order by t.user_name  
    select t.user_name,
      sum(decode(t.course, '语文', score,null)) as CHINESE,
      sum(decode(t.course, '数学', score,null)) as MATH,
      sum(decode(t.course, '英语', score,null)) as ENGLISH
    from test_tb_grade t
    group by t.user_name
    order by t.user_name
     

     

    1.3、延伸

     

    如果要实现对各门功课的不同分数段进行统计,效果图如下:


                    

     

    具体的实现sql如下:

    select t2.SCORE_GP,   
    1.   sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,   
    2.   sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,   
    3.   sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH   
    4. from (   
    5.   select t.course,   
    6.          case when t.score  <60 then '00-60'  
    7.               when t.score >=60 and t.score <80  then '60-80'  
    8.               when t.score >=80 then '80-100' end as SCORE_GP,   
    9.          count(t.score) as COUNTNUM   
    10.   FROM test_tb_grade t   
    11.   group by t.course,    
    12.         case when t.score  <60  then '00-60'  
    13.               when t.score >=60 and t.score <80  then '60-80'  
    14.               when t.score >=80 then '80-100' end  
    15.   order by t.course ) t2   
    16. group by t2.SCORE_GP   
    17. order by t2.SCORE_GP  
    select t2.SCORE_GP,
      sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,
      sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,
      sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH
    from (
      select t.course,
             case when t.score  <60 then '00-60'
                  when t.score >=60 and t.score <80  then '60-80'
                  when t.score >=80 then '80-100' end as SCORE_GP,
             count(t.score) as COUNTNUM
      FROM test_tb_grade t
      group by t.course, 
            case when t.score  <60  then '00-60'
                  when t.score >=60 and t.score <80  then '60-80'
                  when t.score >=80 then '80-100' end
      order by t.course ) t2
    group by t2.SCORE_GP
    order by t2.SCORE_GP
    
     

    [二]、列转行

     

    1.1、初始测试数据

            表结构:TEST_TB_GRADE2

    create table TEST_TB_GRADE2   
    1. (   
    2.   ID         NUMBER(10) not null,   
    3.   USER_NAME  VARCHAR2(20 CHAR),   
    4.   CN_SCORE   FLOAT,   
    5.   MATH_SCORE FLOAT,   
    6.   EN_SCORE   FLOAT  
    7. )  
    create table TEST_TB_GRADE2
    (
      ID         NUMBER(10) not null,
      USER_NAME  VARCHAR2(20 CHAR),
      CN_SCORE   FLOAT,
      MATH_SCORE FLOAT,
      EN_SCORE   FLOAT
    )
     

            初始数据如下图:


           

     

    1.2、 如果需要实现如下的查询效果图:


                          

     

    这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下:

    select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2    
    1. union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2    
    2. union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2    
    3. order by user_name,COURSE   
    select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2 
    union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2 
    union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2 
    order by user_name,COURSE 

     

     也可以利用【 insert all into ... select 】来实现,首先需要先建一个表TEST_TB_GRADE3:

    create table TEST_TB_GRADE3     
    1.     (    
    2.       USER_NAME VARCHAR2(20 CHAR),     
    3.       COURSE    VARCHAR2(20 CHAR),     
    4.       SCORE     FLOAT     
    5.     )    
    create table TEST_TB_GRADE3  
        ( 
          USER_NAME VARCHAR2(20 CHAR),  
          COURSE    VARCHAR2(20 CHAR),  
          SCORE     FLOAT  
        )  

     再执行下面的sql:

     

    insert all  
    1. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)   
    2. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)   
    3. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)   
    4. select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;   
    5. commit;  
    insert all
    into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)
    into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)
    into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)
    select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;
    commit;

     别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行了。

     

     

    本文连接:http://sjsky.iteye.com/blog/1152167

    展开全文
  • VBA 使用Excel数据库行转列

    千次阅读 2016-06-07 11:34:36
    最近工作有需求,要从Excel表提取一些统计信息,因为需求变动可能比较频繁,所以用VBA来做了。调试数据操作部分颇费周折,涉及到行转列问题,特记录一下。

    最近工作中有需求,要从Excel表中提取一些统计信息,因为需求变动可能比较频繁,所以用VBA来做了。调试数据操作部分颇费周折,特记录一下。

    1.数据源文件:[D:\4月份统计表.xlsx]:


    2.最终的统计表格式:


    3.计算规则:


    要求:

    A. 外观等级A,且WLD在451.5-458之间,且LOP在82-200之间判定为OK,其他为NG。

    B.将NG的按照表面等级→WLD→LOP的优先顺序,判定是哪一项引起的不良,更新到数据源的 [筛选结果] 中。

    C.在统计表中按照入库日期分组,统计总数量、OK数量、NG数量,以及NG产品中外观等级、WLD、LOP各占多大比例。

    4.代码:

    Option Explicit
    
    Public Sub DataInquire()
        Dim strCnn As String, sqlStr As String, subSqlStr As String, strSql(1 To 4) As String '判断条件 1=OK,2=外观等级NG,3=WLDNG,4=LOPNG
        Dim MainBook As Workbook
        Dim Arr() As Variant, rngA As Range, rngB As Range
        Dim cnn As Object, rs As Object
        Dim i As Long, itemStr(1 To 4) As String '判定标志 1=OK,2=外观等级,3=WLD,4=LOP
        Dim sourceFile As String
        
        Set cnn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")
        Set MainBook = ThisWorkbook
        sourceFile = "D:\4月份统计表.xlsx"
        strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sourceFile + "';Extended Properties='Excel 12.0;HDR=YES,IME=1'"
            
        With ThisWorkbook.Worksheets(1)
            strSql(1) = " And [外观等级] ='A' And [WLD] >=451.5 And [WLD] <=458 And LOP>=82 And LOP<200"
            strSql(2) = " And [外观等级] <>'A'"
            strSql(3) = " And ([WLD] <451.5 or [WLD] >458)"
            strSql(4) = " And (LOP>=82 or LOP<200"
            itemStr(1) = "OK"
            itemStr(2) = "外观等级"
            itemStr(3) = "WLD"
            itemStr(4) = "LOP"
            Set rngA = .Range("B" & .Cells(6000, "B").End(xlUp).Row + 1).Resize(1, 12) '//找到写数据的位置
        End With
        
        cnn.Open strCnn
        If cnn.State = 1 Then
            For i = 4 To 1 Step -1 '//更新筛选结果
                sqlStr = "update [数据源$] set [筛选结果]= '" & itemStr(i) & "' where 1=1 " & strSql(i)
                cnn.Execute sqlStr
            Next i
            '//Excel/Access不支持case when then end结构,要用IIF来代替
    '        subSqlStr = "(select [序号], case [筛选结果] when 'OK' then 1 end as [OK]," & _
    '                " case [筛选结果] when '外观等级' then 1 end as [外观等级]," & _
    '                " case [筛选结果] when 'WLD' then 1 end as [WLD]," & _
    '                " case [筛选结果] when 'LOP' then 1 end as [LOP]," & _
    '                " from [数据源$]) B "
    '       ///
            subSqlStr = "(select [衬底编号], iif( [筛选结果] = 'OK',1,0 ) as [OK]," & _
                    " iif( [筛选结果] = '外观等级',1,0 ) as [外观等级]," & _
                    " iif( [筛选结果] = 'WLD',1,0 ) as [WLD]," & _
                    " iif( [筛选结果] = 'LOP',1,0 ) as [LOP]," & _
                    " from [数据源$]) B "
            
            sqlStr = "select A.[入库日期],count(A.[入库日期]) as 总数量,sum(B.[OK]) as OK,(count(A.[入库日期])-sum(B.[OK])) as NG,sum(B.[外观等级])/(count(A.[入库日期])-sum(B.[OK]))," & _
                    " sum(B.[WLD])/(count(A.[入库日期])-sum(B.[OK])),sum(B.[LOP])/(count(A.[入库日期])-sum(B.[OK]))" & _
                    "from [数据源$] A, " & subSqlStr & " where A.[序号]=B.[序号] " & _
                    "group by A.[入库日期] order by A.[入库日期] asc"
            Set rs = cnn.Execute(sqlStr)
            If Not rs.EOF Then
                Arr = WorksheetFunction.Transpose(rs.GetRows())
                Set rngA = rngA.Resize(UBound(Arr, 1), 12)
                rngA = Arr
                rngA.Borders.LineStyle = xlContinuous
                rngA.Resize(, 1).Offset(, 4).Resize(, 8).NumberFormatLocal = "0.00%"
            End If
            Set rs = Nothing
        End If
        cnn.Close
    End Sub
    


    说明:

    A.要将[筛选结果]做细项分析,就要用到行转列。而Excel/Access中没有case when then end 结构,更没有pivot可以用,幸好有IIF可用。

    B.Transpose函数使用有些限制,要求结果集不超过1024行[未验证],且结果集不包含null[已验证]。

    C.Excel作为数据库,查询速度明显比Access数据库以及SqlServer、Oracle速度慢很多。5万多行数据,要查询大约20秒了。

    D.Excel表作为数据库使用,select和update可正常使用,但insert和delete不能用。使用select into 可以创建新的Excel工作表。

    展开全文
  • 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子句中的一个表产生了关系,取出了表中的一列作为数据.

    展开全文
  • 数据库行转列

    千次阅读 2019-05-24 11:48:00
    要把数据变成,一目了然的看到一个学生的所有科目成绩,SQL如下: SELECT Name, MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '...
  • oracle数据库实现行转列和列转行的Sql语句,知道的不知道的,大家一块来重温一下吧! 列转行 Create table test (name char(10),km char(10),cj int)     insert test values('张三','语文',80)...
  • 1.创建数据库表,为表字段添加数据 #创建数据库表结构 create table tb_student_grade( id int(10) not null auto_increment primary key, s_name varchar(20) default null, course varchar(20) default null, ...
  • 数据库行转列简单例子

    千次阅读 2016-08-30 17:01:33
    数据库行转列简单例子
  • sysbase 数据库行转列

    2015-11-30 08:35:40
    a/b两张表,是一对多的关系,一条sql语句,查询出a表和对应b表的字段拼接 a表: id name 1 小明 2 小红 3 小丽 b表: id aid bname 1 1 ba 2 1 bv 3 3 vb 4 3 vc 结果: a.id a.name b.id b.bname ...3 小丽 3,4 vb,vc
  • MySQL数据库行转换为的2种方法

    千次阅读 2017-02-15 19:27:40
    写在前面的话: 本博客只是为了记录我在工作遇到的一些bug... 言归正传,今天在公司做个人工资结算查询的时候,需要从数据库取值(数据库为MySQL),显示为类似工资条的效果,但是数据库的表结构需要进行转换为
  • ![图片说明](https://img-ask.csdn.net/upload/201709/16/1505493940_756387.png) DATA_VALUE, COURSE ,QTY
  • 首先需要创建一张表比如: 下面是建表和表数据的SQL语句:新建一个tb.txt...然后到数据库运行: /* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server ...
  • 我的一张表里有三个字段,id,a,b。id字段不是唯一的,a字段里有RE开头和RG开头的值,b字段是它对应的结果,有0和1两种。我现在想写sql语句,查询结果为,以RE和RG开头的两种值分成两个字段RE.a和RG.a。...
  •  行转列,列转行是我们在开发过程经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。 用传统的方法,比较好理解。层次清晰,而且比较习惯。 但是...
  • 在许多的互联网项目当中,报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在SQLServer当中如何使用PIV...
  • 数据库行查询 数据库行查询 数据库行查询 数据库行查询 数据库行查询 数据库行查询
  • oracle数据库 行转列 用decode函数实现

    千次阅读 2017-08-17 13:36:58
    drop table student --删除表 create table student --创建表 ( name varchar2(50), class varchar2(50), score int ) --给表新增数据 insert into student(name,class,score)values('小明','语文',80);...
  • 数据库--mysql动态行转列

    千次阅读 2015-05-26 16:41:19
    create table table1(  col1 char(2),  col2 char(2),  col3 int ); insert into table1 values ('A1','B1',9), ('A2','B1',7), ('A3','B1',4), ('A4','B1',2), ...('A2','B2',9),
  • 数据库字段存JSON,何不弄张表,行转列
  • DB2数据库多行一转换成 一

    千次阅读 2018-11-14 09:51:12
    在db2遇到多行一转为一的需求时,用db2函数 LISTAGG可以实现该功能。语法如下: SELECT [分组的字段],LISTAGG([需要聚合的字段名], ',') FROM [表名] GROUP BY [分组的字段名] ; 1:构造一张临时表...
  • 数据库】HIVE SQL函数之行转列

    千次阅读 2019-05-25 22:08:29
    小白之前经常在工作遇到需要行转列的情况,这次就总结一下,之前遇到过的各种情况,及在各种情况下的函数应用。 场景: 现有一个表,表示一个房型在未来60天的是否可订情况,由于房型的数据量是庞大的,可能达到...
  • 数据库数据库

    千次阅读 2016-05-16 11:19:13
    卢东明认为数据库发展已经到了一个新阶段,数据库市场需要细分,数据库不再满足所有的需求,而有很多需求需要通过内存数据库数据库解决,数据库在数据分析、海量存储、BI这三个领域有自己独到。...
  • 数据库多行

    千次阅读 2014-05-22 13:37:11
    如题: ...max(case when name='1' then [temp] else null end) as temp1 ..., max(case when name='2' then [temp] else null end) as temp2, ... max(case when name='3' then [temp] else null end) as
  • mysql数据库行转多行

    千次阅读 2016-07-12 13:41:59
    即同一个列中存储了多个属性值(具体结构见下表)。 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。 表数据: ID Value 1 tiny,small,big 2 ...
  • 行转列问题

    2012-01-05 14:01:21
    sql行转列问题,可以解决数据库中显示数据显示格式
  • 数据库中行转列的实现

    千次阅读 2016-12-22 17:18:47
    /***这次练习的主题,行转列,列转行***/select * from Scores 2,得到姓名,通过group by select Student as '姓名'from Scoresgroup by Studentorder by Student 3,再加上max, case……...
  • select running_time, running_user, algorithm_id, algorithm_list_id, ...在该过程需要注意的是我们调用了oracle数据库的系统函数wm_connect()函数,但是该函数又要必须调用to_char(列名)才能生效
  • sql ,行转列

    2013-01-23 15:14:50
    sql 行转列 代码 数据库 例子

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 265,163
精华内容 106,065
关键字:

数据库中的行转列、