SQL - Do not show duplicate value in one column

Question

Working in SQL Server 2016.

I have a query to check the items against the inventory to see where, if any, there is any stock. There are different inventory "zones" (1/2/3 in the example).

This is my code:

   select distinct
    a.item , sum(a.order_qty) 'ord_qty' , a.loc_1_qty , a.loc_2_qty, a.loc_3_qty
    from (
    select
    o.item, o.order_qty, loc1.loc_1_qty, loc2.loc_1_qty, loc3.loc_1_qty
 from order o
    full join (select location + ' - ' + sum(available_qty) 'loc_1_qty' from inventory where location like '1') loc1
    on loc1.item = o.item
    full join (select location + ' - ' + sum(available_qty) 'loc_2_qty' from inventory where location like '2') loc2
    on loc2.item = o.item
    full join (select location + ' - ' + sum(available_qty) 'loc_3_qty' from inventory where location like '3') loc3
    on loc3.item = o.item
) a
group by a.item, a.loc_1_qty, a.loc_2_qty, a.loc_3_qty
order by a.item

Producing something like below:

| ITEM         | ORD_QTY| LOC_1_QTY    |   LOC_2_QTY  |   LOC_3_QTY  |
|--------------|--------|--------------|--------------|--------------|
|  TSHIRT-A.S  |    2   | LOC_AAA - 5  |     NULL     | LOC_BBB - 5  |
|  TSHIRT-B.M  |    2   |    NULL      |  LOC_CCC - 8 | LOC_DDD - 1  |
|  TSHIRT-B.M  |    2   |    NULL      |  LOC_CCC - 8 | LOC_EEE - 2  |
|  TSHIRT-B.M  |    2   |    NULL      |  LOC_CCC - 8 | LOC_FFF - 7  |
|  PANTS-A.S   |    1   |    NULL      |  LOC_GGG - 1 |      NULL    |
|  PANTS-A.M   |    3   |    NULL      |  LOC_HHH - 1 |  LOC_III - 1 |
|  PANTS-A.M   |    3   |    NULL      |  LOC_HHH - 1 |  LOC_JJJ - 3 |
|  PANTS-A.M   |    3   |    NULL      |  LOC_HHH - 1 |  LOC_KKK - 5 |
|  PANTS-A.L   |    5   | LOC_LLL - 1  |     NULL     |     NULL     |

If an item is in several different locations in one zone, the values in the other columns will duplicate.

Is it possible at all using just SQL, to eliminate duplicate values in a particular column/all columns, if its value is the same as the previous row?

Or would it be more achievable in Excel instead?

| ITEM         | ORD_QTY| LOC_1_QTY    |   LOC_2_QTY  |   LOC_3_QTY  |
|--------------|--------|--------------|--------------|--------------|
|  TSHIRT-A.S  |    2   | LOC_AAA - 5  |     NULL     | LOC_BBB - 5  |
|  TSHIRT-B.M  |    2   |    NULL      |  LOC_CCC - 8 | LOC_DDD - 1  |
|  -           |    -   |    NULL      |     -        | LOC_EEE - 2  |
|  -           |    -   |    NULL      |     -        | LOC_FFF - 7  |
|  PANTS-A.S   |    1   |    NULL      |  LOC_GGG - 1 |      NULL    |
|  PANTS-A.M   |    3   |    NULL      |  LOC_HHH - 1 |  LOC_III - 1 |
|  -           |    -   |    NULL      |     -        |  LOC_JJJ - 3 |
|  -           |    -   |    NULL      |     -        |  LOC_KKK - 5 |
|  PANTS-A.L   |    5   | LOC_LLL - 1  |     NULL     |     NULL     |

You should do this in the application layer. And the `full join`s are probably not necessary. — Gordon Linoff, Jul 19 '18 at 15:09

score 0 · Answer 1 · answered Jul 19 '18 at 15:21

You should look into changing your FULL JOIN statements into other formats. Please reference this link to cover the different types of join statements. Additionally, try using it in conjunction with SELECT DISTINCT. It looks like you're already families with that expression, but with the correct application of JOINs, it might help solve your problem.

SQL - Do not show duplicate value in one column

1 Answers1