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

My bad please ignore my earlier post.

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

actually the sum would go around the entire case

Jeff

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

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

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