how to display data in one column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to display data in one column

Author

Topic

tips54
Starting Member

5 Posts

Posted - 2009-10-02 : 12:29:09

Hello all,

I am working on this AR report which is simple (I thought). i'm working on a report that require AR balance by months going back 1 year. I had to create a view to capture AR by periods, than I created another view to add the periods for each month. see code snipet.. The issue i'm having is the data is displayed by individual rows, the reason is becuase I have each column labeled P1, P2, P3 ... when I join this view to a final query I end up with other columns I want (Like customer_id) plus P1, P2 listed accross instead of P1, P2 displayed vertically.

The code at the bottem result is:
Customer_ID P1 P2 P3 P4 P5 P6
107444 100 200 300 100 600 200

Expected result:
Customer_ID AR_bal_periods
107444 100
107444 200
107444 300
107444 100
107444 600
107444 200

*** The true final would be: ***

Customer_id Period Year1 Amt_paid Invoice_sales Margin AR_bal_periods
107444 5 2009 1000 5000 xx 4000
107444 6 2009 1000 2000 xx 6000
107444 7 2009 7000 8000 xx 7000
Like I said my issue right now is getting the period view to display correctly.

Thanks in advance.

CODE...
***** ALTER view vw_ar_balance
select distinct P21_view_customer_credit_history.customer_id,
sum (case when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 0 and 30
then invoiced_sales - amount_paid
else 0
end) as [a],
sum (case
when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 31 and 60
then invoiced_sales - amount_paid
else 0
end) as [b],
sum (case
when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 61 and 90
then invoiced_sales - amount_paid
else 0
end) as [c],
sum (case
when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 91 and 120
then invoiced_sales - amount_paid
else 0
end) as [d],
sum (case
when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 121 and 150
then invoiced_sales - amount_paid
else 0
end) as [e],
sum (case
when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 151 and 180
then invoiced_sales - amount_paid
else 0
end) as [f]
from P21_view_customer_credit_history
group by P21_view_customer_credit_history.customer_id
*****
ALTER view vw_ar_balance2 as
select customer_id,
a+b+c+d+e+f as P1,
b+c+d+e+f as P2 ,
c+d+e+f as P3,
d+e+f as P4,
e+f as P5,
f as P6,
from vw_ar_balance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:32:20

are you using sql 2005 or later? if yes, use UNPIVOT for this. see below for example

http://blog.sqlauthority.com/2008/05/29/sql-server-unpivot-table-example/

tips54
Starting Member

5 Posts

Posted - 2009-10-02 : 12:50:55

I forgot to mention I'm on SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:57:31

then use union all


SELECT customer_id,'P1' as period,P1 AS Val
FROM vw_ar_balance
UNION ALL
SELECT customer_id,'P2' as period,P2
FROM vw_ar_balance
UNION ALL
SELECT customer_id,'P3' as period,P3
FROM vw_ar_balance
...
SELECT customer_id,'P6' as period,P6
FROM vw_ar_balance