SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PBoy
Starting Member

United Kingdom
19 Posts

Posted - 11/13/2012 :  10:10:25  Show Profile  Reply with Quote
Hi All,

I'm looking to have some row Pivot into colums based on a case statement I have tried to use some examples on the net but just cant work it out the query below is what my source script is :
quote:

SELECT t.period,
Sum(t.amount) AS amount,
Count(DISTINCT t.voucher_no) AS trans,
( CASE
WHEN t.period = c.curr_period THEN '<30'
WHEN t.period = c.curr_period - 1 THEN '>30<60'
WHEN t.period = c.curr_period - 2 THEN '>60<90'
ELSE '>90'
END ) AS break_down
FROM awfvitin t
INNER JOIN acrclient c
ON c.Client = t.client
WHERE t.client = 'RC'
GROUP BY t.period,
c.curr_period


The data comes back like :
period,amount,trans,break_down

But I would like it to come back like:
period,amount,trans,<30,>30<60,>60<90,>90

Any help would be great

Cheers
Patrick

PBoy
Starting Member

United Kingdom
19 Posts

Posted - 11/14/2012 :  12:08:47  Show Profile  Reply with Quote
Got this working thanks anyways and for anyone that is intrested here is the sql below.

SELECT
[status],
[period],
[trans],
[less_30],
[between_30_60],
[between_60_90],
[greater_90]
FROM (SELECT 'Expenses In Workflow' AS status,
t.period,
Sum(t.amount) AS amount,
Count(DISTINCT t.voucher_no) AS trans,
( CASE
WHEN t.period = c.curr_period THEN 'less_30'
WHEN t.period = c.curr_period - 1 THEN 'between_30_60'
WHEN t.period = c.curr_period - 2 THEN 'between_60_90'
ELSE 'greater_90'
END ) AS break_down
FROM awfvitin t
INNER JOIN acrclient c
ON c.Client = t.client
WHERE t.client = 'RC'
GROUP BY t.period,
c.curr_period) AS p PIVOT (Max (amount) FOR break_down IN ([less_30], [between_30_60], [between_60_90], [greater_90])) AS ap_kpi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000