locked
merge column as Row base on two criteria RRS feed

  • Question


  • TABLE 1. 

    TBLINVOICENO
    INVOICEID INVOICENO REFERENCENO
    203 1563 12364
    204 12536 12364
    205 197 12364
    207 86 145
    TABLE 2 . TBLINVOICEINFO
    INFOID     INVOICEID    COMCODE PRECODE        
    ITEMS                             QTY AMOUNT
    70 204 40091100    00 RUBBER WATER TEMP PIPE 10 20
    71 204 39269090    00 PLASTIC DRUM 2 200
    73 203 87089090    20 STERRING WHEEL COVER 10 100
    74 203 87089090    20 STERRING WHEEL COVER 100

    200

    75 203 39269090
       00
    PLASTIC ITEMS 300 100
    77 205 39269090    00 PLASTIC DRUM 100 100
    78 204 87089090    10 DIFUSER 10 50
    79 204 87089090    10 DIFUSER 10 50
    80 207 64345800    10 WHITE WINE 52 200
    81 207 12345678    10 CHICKEN WINGS 52 800
    82 207 64345800    20 RED WINE 52 200

    83

    204 40091100
       20 RUBBER WATER TEMP PIPE 10 20
    84 207 64345800    10 RED WINE 10 100


    QUERY 1

    1. SELECT DISTINCT COMCODE PRECODE, ITEMS, SUM(QTY)AS QTY, SUM(AMOUNT) AS AMOUNT

    FROM TBLINVOICENO INNER JOIN TBLINVOICEINFO
    WHERE TBLINVOICENO.REFERENCENO=12364
    Group By COMCODE, PRECODE, ITEMS


    COMCODE PRECODE ITEMS QTY AMOUNT
    40091100    00 RUBBER WATER TEMP PIPE 10 20
    39269090    00 PLASTIC DRUM, PLASTIC ITEM 402 400
    87089090    20 STERRING WHEEL COVER 110 300

    87089090

       10 DIFUSER     20 100
    40091100     20 RUBBER WATER TEMP PIPE 10 20
    HOW CAN I ACHIEVE THIS RESULT I BEEN TRYING
    Friday, April 17, 2020 9:39 PM

All replies

  • HOW CAN I ACHIEVE THIS RESULT I BEEN TRYING

    And what's the logic for the result?

    Please post table design as DDL and some sample data as DML statement.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, April 18, 2020 7:08 AM
  • This requires SQL 2017 or later. (Always include the version of SQL Server you are using when asking a question!)

    ; WITH CTE AS (
        SELECT comcode, precode, items. SUM(Qty) AS qty, SUM(Amount) AS amount
        FROM   tblinvoiceinfo
        GROUP  BU comcode, precode, items
    )
    SELECT comcode, precode, string_agg(items, ', ') as items, qty, amount
    FROM   CTE
    GROUP  BY comcode, precode, qty, amount

    I could not find any relation between you desired result and the first table, so I ignored it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Saturday, April 18, 2020 8:57 AM
  • i try to run this but it give me the same result

    table one has several invoice numbers

    table two has the invoice information for each invoice that is the relationship between the two tables

    i will like to consolidate each of the items in to one with commas base on the unique comcode and precode

    which will give the result in third table

    Monday, April 20, 2020 1:59 AM
  • If you want tested queries, you should post CREATE TABLE statements for your tables and INSERT statments with sample data, so that we easily can copy and paste into a query window. Without that, you may get an untested solution. Which you indeed got in this case.

    As I noted in my previous post, I was not able to discern any related between the expected result and the first table, which is why I chose to ignore that table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, April 20, 2020 9:27 PM