Teradata SQL Pivot Multiple Occurrences into Additional Columns

teradata sql pivot multiple occurrences into additional columns

Unfortunately Teradata doesn't have a PIVOT function but you can use an aggregate function with a CASE expression to get the result.

select id,
    max(case when seq =1 then result end) result1,
    max(case when seq =2 then result end) result2,
    max(case when seq =3 then result end) result3
from
(
    select id, res, row_number() over(partition by id order by result) seq
    from yourtable
) d
group by id
order by id;

If you have more values for each ID, then you can add more CASE expressions.

Accomplish pivot in teradata sql

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

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

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

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: Calculate number of successive occurrences of error code (and reset on non occurrence)

You can subtract row_number() from the date and aggregate:

select item, err_cd, count(*)
from (select t.*,
             row_number() over (partition by item, err_cd order by run) as seqnum
      from t
     ) t
group by item, err_cd, run - seqnum
order by item, err_cd, min(run);

Pivot table in teradata sql

So i found the answer and i would like to share it with you
First of all you need to recalculate the data to know the counts for id||Value

CREATE TABLE myTable_count as (
SELECT ID, Result, count(1) as countOfResult
FROM myTable 
group by  ID, Result
) WITH DATA 

Then you can do a pivot, but you have to know the names of result's

 SELECT ID,
 sum(CASE WHEN  Result='Value1' then countOfResult else NULL END) Value1,
 sum(CASE WHEN  Result='Value2' then countOfResult else NULL END) Value2
 FROM myTable_count
 GROUP BY ID;

If it help you give it a vote up.

Break out nested data within SQL, criteria across multiple rows (similar to dcast in R)

I am trying to identify which IDs only have B and D. I have a query to find both

If this is what you want, you don't need multiple columns:

select id
from table1
where col2 in ('B', 'D')
group by id
having count(distinct col2) = 2;

If you want only 'B' and 'D' and no others, then:

select id
from table1
group by id
having sum(case when col2 = 'B' then 1 else 0 end) > 0 AND
       sum(case when col2 = 'C' then 1 else 0 end) > 0 AND
       sum(case when col2 not in ('B', 'D') then 1 else 0 end) = 0;

If there are only two columns, you can also easily pivot the values using aggregation:

select id, min(col2), nullif(max(col2), min(col2))
from table1
group by id;

---

Related Topics