×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Florida

If you want do it in One SQL, then there is some limitation. If you know all the colume values, then you can do it. Otherwise you can not. Please check inside.

funnyguy(喜欢加拿大)
本文发表在 rolia.net 枫下论坛I do not know if I understand your question clearly, but I can give you one example how to deal with this kind of problem.

Table A,
Student No, Course No, Score
1111, A11 80
2222, A11 85
3333, A11 70
1111, A12 50
2222 A12 60
3333 A12 40
2222 A13 50
3333 A13 90

You want to turn into the format like this
Student NO, Score of CourseA1, Score of CourseA2, Score of CourseA3
1111 80 50
2222 85 60 50
3333 70 40 90

Here you know the course will only be A1,A2,A3, this is the key point here.
select aliasppp.studentNO,
( select aliasp.score
from A aliasp
where aliasp.studentNO = aliasppp.studentNO
and aliasp.course = 'A1) ,
( select aliasp.score
from A aliasp
where aliasp.studentNO = aliasppp.studentNO
and aliasp.course = 'A2) ,
( select aliasp.score
from A aliasp
where aliasp.studentNO = aliasppp.studentNO
and aliasp.course = 'A3)
FROM A aliasppp更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#1405263@0)
Last Updated: 2003-10-9
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / small question: how to turn column into row? I use Oracle Thanks -alan_wang99(Baby); 2003-10-3 {47} (#1397097@0)
    I had that script, but just can't find it now.
    • What you mean here? turn column names into rows or turn one row data into rows, each row contains a column data? -anno(烦人); 2003-10-3 (#1397103@0)
      • Hehe -alan_wang99(Baby); 2003-10-3 {190} (#1397130@0)
        A column X has value a1,a2,b1,B2 .... etc.
        select X from table, the result will display like
        X
        ---
        a1
        a2
        b1
        B2 ....
        and What I want is to display like this
        a1 a2 b1 b2

        Thanks,
        • Open the table multi-times -handd(大熊猫®); 2003-10-9 (#1405162@0)
        • If you want do it in One SQL, then there is some limitation. If you know all the colume values, then you can do it. Otherwise you can not. Please check inside. -funnyguy(喜欢加拿大); 2003-10-9 {1625} (#1405263@0)
          本文发表在 rolia.net 枫下论坛I do not know if I understand your question clearly, but I can give you one example how to deal with this kind of problem.

          Table A,
          Student No, Course No, Score
          1111, A11 80
          2222, A11 85
          3333, A11 70
          1111, A12 50
          2222 A12 60
          3333 A12 40
          2222 A13 50
          3333 A13 90

          You want to turn into the format like this
          Student NO, Score of CourseA1, Score of CourseA2, Score of CourseA3
          1111 80 50
          2222 85 60 50
          3333 70 40 90

          Here you know the course will only be A1,A2,A3, this is the key point here.
          select aliasppp.studentNO,
          ( select aliasp.score
          from A aliasp
          where aliasp.studentNO = aliasppp.studentNO
          and aliasp.course = 'A1) ,
          ( select aliasp.score
          from A aliasp
          where aliasp.studentNO = aliasppp.studentNO
          and aliasp.course = 'A2) ,
          ( select aliasp.score
          from A aliasp
          where aliasp.studentNO = aliasppp.studentNO
          and aliasp.course = 'A3)
          FROM A aliasppp更多精彩文章及讨论，请光临枫下论坛 rolia.net
          • if it is a single column table, what can you do?... -handd(大熊猫®); 2003-10-9 (#1406114@0)
            • Then it does not make sense to turn it into one row. I do not think you have any real examples having this kind of requirement. Anyway, you still can do it through multi-SQL . -funnyguy(喜欢加拿大); 2003-10-10 (#1407009@0)
              • Can you solve my 'silly' problem...(This would be a interesting script) -handd(大熊猫®); 2003-10-10 {129} (#1407447@0)
                Here is my table in Oracle DB:
                Col
                -----
                1
                2
                3
                4
                5
                6
                7
                I am expecting the output as
                My_Output
                --------------
                1234567
                • If you think it is a stupid question, then why I should bother to fix it. I do not want to be looked as a geek. Do something useful please. -funnyguy(喜欢加拿大); 2003-10-10 (#1407464@0)

More Topics

  • 脑洞大开，突发奇想，用力来传播信息
  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论