| Author |
 Topic  |
|
|
PBoy
Starting Member
United Kingdom
15 Posts |
 Posted - 11/13/2012 : 10:10:25
|
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
15 Posts |
Posted - 11/14/2012 : 12:08:47
|
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 |
 |
|
| |
Topic |
|
|
|
SQL Pivot
