谁能帮我解决下面的sql问题？

发布于 2025-01-14 20:09:22 字数 480 浏览 1 评论 0原文

输入 :-TABLENAME :- T1

ALGO|QTR|VOL
A   Q1  1
A   Q2  2
A   Q3  3
B   Q1  4
B   Q2  5
B   Q3  6
C   Q1  7
C   Q2  8
C   Q3  9

在此处输入图像描述

输出：

ALGO  Q1    Q2  Q3
A      1    2   3
B      4    5   6
C      7    8   9

在此处输入图像描述我尝试在 sql 中执行透视，但给出语法错误。任何人都可以帮助我吗？

原文

INPUT :-TABLENAME :- T1

ALGO|QTR|VOL
A   Q1  1
A   Q2  2
A   Q3  3
B   Q1  4
B   Q2  5
B   Q3  6
C   Q1  7
C   Q2  8
C   Q3  9

enter image description here

OUTPUT:

ALGO  Q1    Q2  Q3
A      1    2   3
B      4    5   6
C      7    8   9

enter image description here
I tried pivot in sql but giving syntax error. Can any one help me for the same ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

女皇必胜 2025-01-21 20:09:22

您可以执行手动/静态数据透视，如下所示：

select
  algo,
  max(case when qtr = 'Q1' then vol end) as q1,
  max(case when qtr = 'Q2' then vol end) as q2,
  max(case when qtr = 'Q3' then vol end) as q3
from t1
group by algo

如果您需要动态数据透视，则可以使用两个 SQL 选择：第一个可以检索可能的列名称；第二个可以检索可能的列名称。使用它，您可以组装第二个查询（使用与此答案中所示相同的形式），该查询将动态生成列。

You can do a manual/static pivot, as in:

select
  algo,
  max(case when qtr = 'Q1' then vol end) as q1,
  max(case when qtr = 'Q2' then vol end) as q2,
  max(case when qtr = 'Q3' then vol end) as q3
from t1
group by algo

If you need the pivot to be dynamic, then you can use two SQL selects: the first one can retrieve the possible column names; with it you can assemble a second query (using the same form as shown in this answer) that will produce columns dynamically.

回复收藏 0 原文

清醇 2025-01-21 20:09:22

我喜欢 Imapaler 的解决方案，它可能比我的更好。

下面是一种可能更昂贵的替代方案，但也展示了一种不同的方法。但不能保证它会表现出更差的性能。数据库系统常常让我感到惊讶。

select a.algo, a.vol as Q1, b.vol as Q2, c.vol as Q3
  from T a, T b, T c
 where a.algo = b.algo
   and b.algo = c.algo
   and a.qtr = 'Q1'
   and b.qtr = 'Q2'
   and c.qtr = 'Q3';

注意：有些人绝对喜欢连接运算符。将上面的内容转换为使用连接运算符会很容易。逻辑保持不变。

I like The Imapaler's solution and it is likely better than mine.

Below an alternative that might be a lot more expensive, but also shows a different approach. It is not guaranteed that it will show a worse performance though. Database systems often manage to surprise me.

select a.algo, a.vol as Q1, b.vol as Q2, c.vol as Q3
  from T a, T b, T c
 where a.algo = b.algo
   and b.algo = c.algo
   and a.qtr = 'Q1'
   and b.qtr = 'Q2'
   and c.qtr = 'Q3';

Note: some people are absolutely in love with the join operator. It'll be easy to convert the above to use the join operator. The logic remains the same.

回复收藏 0 原文

~没有更多了~