SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
, Convert(varchar, resultDate, 101) AS resultDate
FROM [TableA]) AS D
PIVOT(SUM(Amount) FOR resultDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006],[Outstanding])) AS P
Now I want to add two things:
1) Add one more column 'Total' containing the total of amount for each startDate
2) Add a column 'OutStanding' in PIVOT which should contain SUM(Amount) where resultDate IS NULL.
How do I do it?declare @.TableA table (
AccountNumber int,
StartDate datetime,
ResultDate datetime,
amount decimal(10,2)
)
insert into @.TableA values (100, '02/27/2006', '02/28/2006', 300)
insert into @.TableA values (100, '02/28/2006', null, 500)
insert into @.TableA values (100, '03/01/2006', null, 800)
insert into @.TableA values (100, '02/27/2006', null, 100)
insert into @.TableA values (100, '02/27/2006', null, 200);
insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/28/2006', null, 100)
insert into @.TableA values (200, '03/01/2006', null, 200)
insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/27/2006', null, 100);
insert into @.TableA values (300, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (300, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (300, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (300, '02/27/2006', null, 50)
insert into @.TableA values (300, '02/27/2006', null, 100);
insert into @.TableA values (400, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (400, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (400, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (400, '02/27/2006', '03/05/2006', 50)
insert into @.TableA values (400, '02/27/2006', '03/07/2006', 100);
WITH Tot_CTE (AccountNumber, Total, OutStanding)
AS
(
SELECT AccountNumber, SUM(Amount), SUM(case when ResultDate is null then Amount else 0 end)
FROM
@.TableA
GROUP BY AccountNumber
)
SELECT a.*,b.Total,b.OutStanding
FROM (
SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
--, Convert(varchar, resultDate, 101) AS resultDate
,Amount
FROM @.TableA) AS D
PIVOT (SUM(Amount) FOR StartDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006])) AS P
) as a
join
tot_CTE b
on
a.accountNumber = b.AccountNumber|||
PIVOT has lot of restrictions and you cannot generate multiple aggregates for example. So It is easier to do this using a standard SQL query. And it is much more efficient since you can compute all the necessary aggregates in one pass of the data. Try query below. I had to guess the details about your schema and what each column means. But you should get the idea.
SELECT t.AccountNumber
, t.StartDate
, SUM(CASE WHEN t.resultDate IS NULL THEN t.Amount END) as OutStanding
, SUM(CASE t.resultDate WHEN '20060227' THEN t.Amount END) as "20060227"
, SUM(CASE t.resultDate WHEN '20060228' THEN t.Amount END) as "20060228"
...
, SUM(t.Amount) AS Total
FROM TableA AS t
WHERE t.resultDate is null
or t.resultDate between '20060227' and '20060302' -- modify accordingly
GROUP BY t.AccountNumber, t.StartDate -- may need to strip time part depending on your data
