CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mohamed faisal
mohamed faisal

Reputation: 446

How to avoid duplicate rows in my Scenario SQL Server?

Here is my sql query and result. I got the result what I expect. But it's adding additional rows in my result.

My query

Declare @store1001 varchar(10)='1001'
Declare @storeAll varchar(10)='StoreAll'

select ID,StoreID,TotalQtyParent into #tempQuantity
From
(
    select ID,@store1001 StoreID,Sum([TotalQtyParent]) TotalQtyParent
    FROM [HQMatajer].[dbo].[ItemRelationQty]
    where StoreID='1001' and TotalQtyParent is not null
    group by ID

    Union

    select ID,@storeAll StoreID,sum([TotalQtyParent]) TotalQtyParent
    FROM [HQMatajer].[dbo].[ItemRelationQty]
    where StoreID<>'1001' and TotalQtyParent is not null 
    group by ID
) totalquantity

Above query result Result for above query

OutPut Query

  select TotalQty.ID, item.ItemLookupCode,item.Description,
    (
        case when TotalQty.StoreID = '1001' then TotalQty.TotalQtyParent
        else 0 end
    ) WHS,
    (
        case when TotalQty1.StoreID = 'StoreAll' then TotalQty1.TotalQtyParent
        else 0 end
    ) Store

FROM
#tempQuantity TotalQty
INNER JOIN #tempQuantity TotalQty1 on TotalQty.ID=TotalQty1.ID
LEFT JOIN HQMatajer.dbo.Item item on item.id=TotalQty.ID


order by WHS desc,Store desc

Final Output

Final output

you can see the every second record of ID and the value of Store is Zero(0). I don't want to show that row.

Please don't suggest me to add the condition in where clause where store <> 0. It's showing result because of my first query result,There every ID is two times are available. Thanks

UPDATED I removed some columns from my output query to understanding purpose

Upvotes: 0

Views: 62

Answers (1)

cybersin
cybersin

Reputation: 186

Have you considered using PIVOT ?

SELECT 
     ID, 
     [1001] AS WHS, 
     [StoreAll] AS Store
FROM
(
    SELECT 
       ID, StoreID, TotalQtyParent
    FROM #tempQuantity
) AS TotalQty
PIVOT
(
    SUM(TotalQtyParent)
    FOR StoreID IN ([1001], [StoreAll])
) AS PivotTable;

And then just use joins...

Upvotes: 1

Related Questions