CODEWITHSUNDEEP
sqlsql-servert-sql
Christopher Thompson
Christopher Thompson

Reputation: 179

T-SQL Pivot duplication

I have a table with the following structure:

enter image description here

In this table the DayOfWeek=2 is Monday and so on.

I am trying to use a pivot in order to get a result similar to this:

enter image description here

But for some reason in store 25 it generates duplicates with this query:

 SELECT p.Store, 
             CASE WHEN[2] is null THEN 0 ELSE LT END L,
             CASE WHEN[3] is null THEN 0 ELSE LT END M,
             CASE WHEN[4] is null THEN 0 ELSE LT END W,
             CASE WHEN[5] is null THEN 0 ELSE LT END J,
             CASE WHEN[6] is null THEN 0 ELSE LT END V          
             FROM(SELECT DISTINCT
             [DayOfWeek], Store, LT FROM replenishment.[Routes]) AS s 
                           PIVOT 
                           (MAX([DayOfWeek]) FOR[DayOfWeek] in  ([2], [3], [4], [5], [6])) as p 
                           WHERE p.Store=25

The result of this query is this:

enter image description here

How can I get the result without these duplicates?

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

How about using conditional aggregation:

 SELECT p.Store, 
        MAX(CASE WHEN DayOfWeek = 2 THEN LT ELSE 0 END) as L,
        MAX(CASE WHEN DayOfWeek = 3 THEN LT ELSE 0 END) as M,
        MAX(CASE WHEN DayOfWeek = 4 THEN LT ELSE 0 END) as W,
        MAX(CASE WHEN DayOfWeek = 5 THEN LT ELSE 0 END) as J,
        MAX(CASE WHEN DayOfWeek = 6 THEN LT ELSE 0 END) as V
FROM replenishment.[Routes] r
WHERE p.Store = 25
GROUP BY p.Store;

Upvotes: 2

Related Questions