Change table layout

Analytics
Highlighted

Change table layout

I'm new to SQL and hope someone can help me on this request.  

 

I have a table A: 

order_nbrtie_nbrrev_classrev_amtREV_COGS
1011Base SW3288.17COGS
1011Base SW Amort8.68COGS
1011Base Syst WAR nRA120.9COGS
1011Bse HW 10693.76COGS
1011Base SW2439.7REV
1011Base Syst WAR nRA393.08REV
1011Bse HW 9252.57REV
1011SVC PESS1748.71REV
1011SVC UpsWAR nRA270.94REV

 

I want to group by order_nbr, tie_nbr, and list revenue and COGS with one row in below format.  

order_nbrtie_nbrrev_classREVCOGS
1011SVC PESS1748.711748.71
1011Base SW Amort8.688.68
1011Bse HW 10693.769252.57
1011Base Syst WAR nRA393.08120.9
1011SVC UpsWAR nRA270.94270.94
1011Base SW3288.172439.7

 

How to write the code to populate REV and COGS respective? REV is not necessarily higher than COGS. Really appreciate it!! 


Accepted Solutions
Teradata Employee

Re: Change table layout

Depending on your Teradata version you can use either the Pivot function (available in TD16.10 and later) or you can use case statements. Below is an example of each.

 

Not positive if it's what you're looking for as it doesn't exactly line up with your example output which seems to include rows that aren't shown in the example input :)

 

create table test (
	order_nbr int
	,tie_nbr int
	,rev_class varchar(25)
	,rev_amt real
	,rev_cogs char(4)
) no primary index;

--Data from question
insert into test values (101,1,'Base SW',3288.17,'COGS');
insert into test values (101,1,'Base SW Amort',8.68,'COGS');
insert into test values (101,1,'Base Syst WAR nRA',120.9,'COGS');
insert into test values (101,1,'Bse HW ',10693.76,'COGS');
insert into test values (101,1,'Base SW',2439.7,'REV');
insert into test values (101,1,'Base Syst WAR nRA',393.08,'REV');
insert into test values (101,1,'Bse HW ',9252.57,'REV');
insert into test values (101,1,'SVC PESS',1748.71,'REV');
insert into test values (101,1,'SVC UpsWAR nRA',270.94,'REV');

--Aggregate by rev_cog using PIVOT
--Available in 16.10 & 16.20
select * 
from test
pivot (
	sum (rev_amt) 
	
	for rev_cogs in ('REV' AS REV,'COGS' AS COGS)
) dt
order by order_nbr, tie_nbr, rev_class;
-- order_nbr tie_nbr rev_class         REV     COGS     
-- --------- ------- ----------------- ------- -------- 
--       101       1 Base SW            2439.7  3288.17
--       101       1 Base SW Amort        null     8.68
--       101       1 Base Syst WAR nRA  393.08    120.9
--       101       1 Bse HW            9252.57 10693.76
--       101       1 SVC PESS          1748.71     null
--       101       1 SVC UpsWAR nRA     270.94     null

--Aggregate using a case statement
select 
	order_nbr
	, tie_nbr
	, rev_class
	,sum(case when rev_cogs = 'REV' then rev_amt else NULL end) as REV
	,sum(case when rev_cogs = 'COGS' then rev_amt else NULL end) as COGS
from test
group by order_nbr, tie_nbr, rev_class
order by order_nbr, tie_nbr, rev_class;

-- order_nbr tie_nbr rev_class         REV     COGS     
-- --------- ------- ----------------- ------- -------- 
--       101       1 Base SW            2439.7  3288.17
--       101       1 Base SW Amort        null     8.68
--       101       1 Base Syst WAR nRA  393.08    120.9
--       101       1 Bse HW            9252.57 10693.76
--       101       1 SVC PESS          1748.71     null
--       101       1 SVC UpsWAR nRA     270.94     null


drop table test;
1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Change table layout

Depending on your Teradata version you can use either the Pivot function (available in TD16.10 and later) or you can use case statements. Below is an example of each.

 

Not positive if it's what you're looking for as it doesn't exactly line up with your example output which seems to include rows that aren't shown in the example input :)

 

create table test (
	order_nbr int
	,tie_nbr int
	,rev_class varchar(25)
	,rev_amt real
	,rev_cogs char(4)
) no primary index;

--Data from question
insert into test values (101,1,'Base SW',3288.17,'COGS');
insert into test values (101,1,'Base SW Amort',8.68,'COGS');
insert into test values (101,1,'Base Syst WAR nRA',120.9,'COGS');
insert into test values (101,1,'Bse HW ',10693.76,'COGS');
insert into test values (101,1,'Base SW',2439.7,'REV');
insert into test values (101,1,'Base Syst WAR nRA',393.08,'REV');
insert into test values (101,1,'Bse HW ',9252.57,'REV');
insert into test values (101,1,'SVC PESS',1748.71,'REV');
insert into test values (101,1,'SVC UpsWAR nRA',270.94,'REV');

--Aggregate by rev_cog using PIVOT
--Available in 16.10 & 16.20
select * 
from test
pivot (
	sum (rev_amt) 
	
	for rev_cogs in ('REV' AS REV,'COGS' AS COGS)
) dt
order by order_nbr, tie_nbr, rev_class;
-- order_nbr tie_nbr rev_class         REV     COGS     
-- --------- ------- ----------------- ------- -------- 
--       101       1 Base SW            2439.7  3288.17
--       101       1 Base SW Amort        null     8.68
--       101       1 Base Syst WAR nRA  393.08    120.9
--       101       1 Bse HW            9252.57 10693.76
--       101       1 SVC PESS          1748.71     null
--       101       1 SVC UpsWAR nRA     270.94     null

--Aggregate using a case statement
select 
	order_nbr
	, tie_nbr
	, rev_class
	,sum(case when rev_cogs = 'REV' then rev_amt else NULL end) as REV
	,sum(case when rev_cogs = 'COGS' then rev_amt else NULL end) as COGS
from test
group by order_nbr, tie_nbr, rev_class
order by order_nbr, tie_nbr, rev_class;

-- order_nbr tie_nbr rev_class         REV     COGS     
-- --------- ------- ----------------- ------- -------- 
--       101       1 Base SW            2439.7  3288.17
--       101       1 Base SW Amort        null     8.68
--       101       1 Base Syst WAR nRA  393.08    120.9
--       101       1 Bse HW            9252.57 10693.76
--       101       1 SVC PESS          1748.71     null
--       101       1 SVC UpsWAR nRA     270.94     null


drop table test;

Re: Change table layout

Thank you very much!