Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

Database

Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

Hi,

Can anybody help me with the following Please!!!!

The procedure below runs in SQL server, however I can't seem to convert it to Teradata code.

The main problem is that I can't use a different select statement to create each new column. as shown in the code below, I am not sure if it is a syntax problem, or if it is a teradata can't do a similar thing problem.

Any help at this point would be great.

SELECT YEAR,
Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q.YEAR),0),
Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q.YEAR),0),
Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q.YEAR),0),
Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
Q.YEAR),0)
FROM QTRSALES Q
GROUP BY YEAR

Thanks a Mill
Chris

6 REPLIES
Enthusiast

Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

SELECT YEAR,
Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q.YEAR),0),
Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q.YEAR),0),
Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q.YEAR),0),
Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
Q.YEAR),0)
FROM QTRSALES Q
GROUP BY YEAR

select year
, case when (quarter = 1 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q1
, case when (quarter = 2 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q2
, case when (quarter = 3 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q3
, case when (quarter = 4 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q4
from qtrsales Q
group by year

I guess this should work

Enthusiast

Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

My bad please ignore my earlier post.
Teradata Employee

Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

is this what you need? added SUM, so you end up with one row per year

select year
, case when (quarter = 1 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q1
, case when (quarter = 2 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q2
, case when (quarter = 3 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q3
, case when (quarter = 4 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q4
from qtrsales Q
group by year
Teradata Employee

Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

actually the sum would go around the entire case
Enthusiast

Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

So Jeff, Are you talking about something like this:

select year
, SUM(case when (quarter = 1 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q1
, SUM(case when (quarter = 2 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q2
, SUM(case when (quarter = 3 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q3
, SUM(case when (quarter = 4 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q4
from qtrsales Q
group by year
Enthusiast

Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

dont forget to change the qtrsales to Amount in the coalesce.
select year
, SUM(case when (quarter = 1 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q1
, SUM(case when (quarter = 2 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q2
, SUM(case when (quarter = 3 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q3
, SUM(case when (quarter = 4 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q4
from qtrsales Q
group by year