SQL Pivot - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Pivot

Author

Topic

PBoy
Starting Member

22 Posts

Posted - 2012-11-13 : 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

22 Posts

Posted - 2012-11-14 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources