在 teradata sql 中完成数据透视 [英] Accomplish pivot in teradata sql

问题描述

假设我有一个返回如下值的查询:

Say I have a query that returns values like this:

id    type     value
aaa   1a        10
aaa   1b        20
aaa   1c        7
bbb   2a        10
bbb   1a        5

有 > 5000 万行和 240 种可能的类型".我想制作一个数据透视图，其中每个 id 有一行，每个 type 都是它自己的列:

There are > 50 million rows and 240 possible "types". I want to make a pivot where there is one row for each id and each type is it's own column:

id   1a   1b   1c   2a
aaa  10   20   7    
bbb  5              10

我可以在 SQL Server 中执行此操作，但我不知道如何在 Teradata 中执行此操作.列太多了，我无法做出 CASE 陈述.但是，每个不同的 type 都在一个表字段中，如果有帮助的话.

I can do this in SQL server but I don't know how to do it in Teradata. There are too many columns for me to make CASE statements. However, each distinct type is in a table field, if that's any help.

推荐答案

Teradata SQL 中没有数据透视功能.这里回答了一个类似的问题 - teradata sql 将多次出现转换为附加列.

There is no pivot function in Teradata SQL. A similar question was answered here - teradata sql pivot multiple occurrences into additional columns.

为了最好地实现您想要的结果而不必手动写出 250 个案例，您应该在某种循环或集合中使用有序分析函数.尝试从 Teradata Developer Exchange 搜索loop"标签 - http://developer.teradata.com/tag/loop

To best achieve what you wanted without having to write out 250 cases manually, you should use ordered analytical functions in some kind of a loop or a set. Try searching "loop" tag from Teradata Developer Exchange - http://developer.teradata.com/tag/loop

这是我的做法:使用另一种编程语言 (Python) 重复文本/预制 SQL，并将它的两个变量从 1 更改为 250 次 250 次，然后生成完整的长 sql.只重复SELECT DISTINCT id和最后FROM mytable行之间的部分:

Here's how I would do it: Use another programming language (Python) to reiterate over a text/premade SQL and change it's only two variables 250 times, from 1 to 250, and generate the full long sql. Only reiterate the part between SELECT DISTINCT id and last FROM mytable row:

SELECT DISTINCT
id
-- reiteration starts here
,(SELECT SUM(value) -- assuming you have unique types for every id
  FROM (SELECT DISTINCT
    id
    ,value
    ,type
    FROM mytable
    QUALIFY (RANK() OVER(PARTITION BY type ORDER BY id ASC))=1 -- variable 1
    )
) AS type_1 -- variable 2
-- reiteration ends here
FROM mytable

你可以使用这个python:

You can use this python:

for i in range(1,251):
    print " \
,(SELECT SUM(value) -- assuming you have unique types for every id \
FROM (SELECT DISTINCT \
id \
,value \
,type \
FROM mytable \
QUALIFY (RANK() OVER(PARTITION BY type ORDER BY id ASC))=%d -- variable 1 \
) \
) AS type_%d -- variable 2 \
" % (i,i)

这篇关于在 teradata sql 中完成数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！