在 Teradata 中将行转换为 SQL 中的列

发布于 2025-01-10 15:48:13 字数 1224 浏览 3 评论 0原文

我的数据如下所示：

姓名	日期	Bal
John	2022-01-01	10
John	2022-01-02	4
John	2022-01-03	7
David	2022-01-01	13
David	2022-01-02	15
David	2022-01 -03	20

我想要将 Bal 列填充到下面date 列，例如：

Name	2022-01-01	2022-01-02	2022-01-03
John	10	4	7
David	13	15	20

我尝试过，

SELECT 
NAME,
CASE WHEN DATE= '2022-01-01' THEN EOD_BALANCE ELSE NULL END "01-Jan-22",
CASE WHEN DATE= '2022-01-02' THEN EOD_BALANCE ELSE NULL END "02-Jan-22"
FROM TABL1

但没有得到所需的结果。以下是第一个答案中查询的结果：

原文

I have data which looks like this:

Name	Date	Bal
John	2022-01-01	10
John	2022-01-02	4
John	2022-01-03	7
David	2022-01-01	13
David	2022-01-02	15
David	2022-01-03	20

I want the Bal column populated under date column, like:

Name	2022-01-01	2022-01-02	2022-01-03
John	10	4	7
David	13	15	20

What I tried is

SELECT 
NAME,
CASE WHEN DATE= '2022-01-01' THEN EOD_BALANCE ELSE NULL END "01-Jan-22",
CASE WHEN DATE= '2022-01-02' THEN EOD_BALANCE ELSE NULL END "02-Jan-22"
FROM TABL1

but I am not getting the required results. Below are the results from query in first answer:

分享到QQ

分享到微博

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

发布评论

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

江心雾 2025-01-17 15:48:13

您需要在这里进行数据透视查询，这意味着您应该按名称聚合，然后取 CASE 表达式的最大值：

SELECT 
    NAME,
    MAX(CASE WHEN DATE = '2022-01-01' THEN EOD_BALANCE END) AS "01-Jan-22",
    MAX(CASE WHEN DATE = '2022-01-02' THEN EOD_BALANCE END) AS "02-Jan-22",
    MAX(CASE WHEN DATE = '2022-01-03' THEN EOD_BALANCE END) AS "03-Jan-22" 
FROM TABL1
GROUP BY NAME;

You want a pivot query here, which means you should aggregate by name and then take the max of the CASE expressions:

SELECT 
    NAME,
    MAX(CASE WHEN DATE = '2022-01-01' THEN EOD_BALANCE END) AS "01-Jan-22",
    MAX(CASE WHEN DATE = '2022-01-02' THEN EOD_BALANCE END) AS "02-Jan-22",
    MAX(CASE WHEN DATE = '2022-01-03' THEN EOD_BALANCE END) AS "03-Jan-22" 
FROM TABL1
GROUP BY NAME;

回复收藏 0 原文

~没有更多了~