All Forums Database
chrisducci 1 post Joined 02/08
11 Feb 2008
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 YEARThanks a Mill Chris

11 Feb 2008

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 QGROUP BY YEARselect 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 Q4from qtrsales Qgroup by yearI guess this should work

11 Feb 2008

My bad please ignore my earlier post.

j355ga 100 posts Joined 12/05
11 Feb 2008

is this what you need? added SUM, so you end up with one row per yearselect 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 Q4from qtrsales Qgroup by year

Jeff

j355ga 100 posts Joined 12/05
11 Feb 2008

actually the sum would go around the entire case

Jeff

oalfonso 12 posts Joined 02/08
13 Feb 2008

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 Q4from qtrsales Qgroup by year

13 Feb 2008

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 Q4from qtrsales Qgroup by year

You must sign in to leave a comment.