Pivot query genetrating duplicate rows

Question

My pivot query is generating duplicate rows. Can anyone solve this ?

DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Columns AS VARCHAR (MAX)
DECLARE @ColumnsRollup AS VARCHAR (MAX)

SELECT @Columns =
COALESCE(@Columns + ', ','')+ QUOTENAME(ProjectId)
FROM
(
    SELECT distinct ProjectId 
    FROM tblCPS where MWM=0
) AS B
ORDER BY B.ProjectId

SELECT @ColumnsRollup =
COALESCE(@ColumnsRollup + ', Sum(','Sum(')+ QUOTENAME(cast(ProjectId as varchar(10)))+') as Project'+cast(ProjectId as varchar(10))
FROM
(
    SELECT distinct ProjectId 
    FROM tblCPS where MWM=0
) AS B
ORDER BY B.ProjectId

SET @SQL = '
            WITH PivotData AS
            (
               SELECT  ContractorId, ProjectId, Amount,SUM(Amount) OVER (PARTITION BY ContractorId )/2 as ''Total''
                FROM tblCps 
            )
            SELECT case when ContractorId is not null then cast(ContractorId as varchar(10))
                else ''Totals'' end ContractorId, ' + @ColumnsRollup+ ',Total
            FROM
            (
                SELECT ContractorId, ' + @Columns + ',Total
                FROM PivotData
                PIVOT
                (
                    SUM(Amount)
                    FOR ProjectId IN (' + @Columns + ')
                ) AS PivotResult
            ) src 
            GROUP BY ContractorId,Total with rollup'

EXEC (@SQL)

Here is the result i am getting

1   10.25   20.35   NULL    NULL    NULL    NULL    NULL    NULL    NULL    30.60
1   10.25   20.35   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
10  NULL    NULL    NULL    NULL    NULL    NULL    303.15  313.25  323.35  939.75
10  NULL    NULL    NULL    NULL    NULL    NULL    303.15  313.25  323.35  NULL
2   40.55   NULL    30.45   50.65   NULL    NULL    NULL    NULL    NULL    121.65
2   40.55   NULL    30.45   50.65   NULL    NULL    NULL    NULL    NULL    NULL
3   70.85   60.75   NULL    NULL    172.00  NULL    NULL    NULL    NULL    303.60
3   70.85   60.75   NULL    NULL    172.00  NULL    NULL    NULL    NULL    NULL
4   101.15  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    101.15
4   101.15  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
5   111.25  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    111.25
5   111.25  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
6   394.35  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    394.35
6   394.35  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
7   151.65  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    151.65
7   151.65  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
8   161.75  171.85  181.95  192.05  202.15  212.25  222.35  232.45  242.55  1819.35
8   161.75  171.85  181.95  192.05  202.15  212.25  222.35  232.45  242.55  NULL
9   NULL    252.65  262.75  272.85  NULL    282.95  293.05  NULL    NULL    1364.25
9   NULL    252.65  262.75  272.85  NULL    282.95  293.05  NULL    NULL    NULL
Totals  1041.80 505.60  475.15  515.55  374.15  495.20  818.55  545.70  565.90  NULL

The rows differ by last column so they are not really duplicates. — Szymon
– Szymon, Commented Nov 7, 2013 at 5:57
columns :ContractorID nvarchar(50) Checked ProjectID nvarchar(50) Checked Amount money Checked MWM bit Checked Expense bit Checked Unchecked — ManzoorPK
– ManzoorPK, Commented Nov 7, 2013 at 6:00
@ManzoorPK Do you need that last column? If not, don't select it and use distinct — Szymon
– Szymon, Commented Nov 7, 2013 at 6:19

Deepshikha · Accepted Answer · 2013-11-07 08:02:19Z

ROLLUP operator generates a result set that shows aggregates for a hierarchy of values in the selected columns.For each value in the columns on the right in the GROUP BY clause, the ROLLUP operation reports combinations of values from the column, or columns, on the left.

Here Pivot is not generating duplicate rows but it's the Roll up operator. The second row which you are thinking is duplicate actually reports a subtotal for all rows having the value 'Total' in the ContractorId dimension. The value null is returned for the 'Total' dimension to show that aggregate reported by the row includes rows with any value of the 'Total' dimension.

Following query should work:

SET @SQL = '
WITH PivotData AS
(
SELECT  ContractorId, ProjectId, Amount,SUM(Amount) OVER (PARTITION BY ContractorId )/2 as ''Total''
FROM tblCps 
)
SELECT case when ContractorId is not null then cast(ContractorId as varchar(10))
else ''Totals'' end ContractorId, ' + @ColumnsRollup+ ',sum(Total)
FROM
(
SELECT ContractorId, ' + @Columns + ',Total
FROM PivotData
PIVOT
(
SUM(Amount)
FOR ProjectId IN (' + @Columns + ')
) AS PivotResult
) src 
GROUP BY ContractorId with rollup'-- generate a report considering values 
                                  -- for contractor id only and not Total
EXEC (@SQL);

Hope this helps!!!

Collectives™ on Stack Overflow

Pivot query genetrating duplicate rows

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related