pivoting data from an existing table data please assist

Database
Enthusiast

pivoting data from an existing table data please assist

Example data I have are as follows:

EXISTING table in Terradata: test

Fields: PersonID, test1, test2, test3... (test xy)

PersonID Test1 Test2 Test 3
1 32 15 12
2 11 3 53
3 12 83 5

What I want (get the max across all tests and value - create table would be fine)

person maxtest value
1 test1 32
2 test3 53
3 test2 83

Can you please assist?
Tags (1)
2 REPLIES
Enthusiast

Re: pivoting data from an existing table data please assist

create volatile table
cps
( id integer,
test1 integer,
test2 integer,
test3 integer)
on commit preserve rows

insert into cps values
(1 , 32, 15,12)

insert into cps values
(2 , 1, 3,53)

insert into cps values
(3 , 12, 83,5)

sel c. id ,
case when f.test = c.test1 then 'test1'
when f.test = c.test2 then 'test2'
when f.test = c.test3 then 'test3'
end subject
, test
from

(sel id id , max(test) test from (
sel id,
case when rn=1 then test1
when rn=2 then test2
when rn=3 then test3 end Test

from cps inner join
( select row_number() over( order by calendar_Date ) as rn from calendar
qualify rn between 1 and 3) a
on 1=1)
finall group by 1 ) f
join
cps c
on
f.id = c.id
Junior Contributor

Re: pivoting data from an existing table data please assist

SELECT
person,
CASE
WHEN test1 >= test2 AND test1 >= test3 THEN 'test1'
WHEN test2 >= test3 THEN 'test2'
ELSE 'test3'
END,
CASE
WHEN test1 >= test2 AND test1 >= test3 THEN test1
WHEN test2 >= test3 THEN test2
ELSE test3
END
FROM test

Dieter