find all variations of code assignment

  • We have orders with at least one code assigned to them. I need to find out all possible variations of those code assignments and count of orders with those variations.

    I have come up with the following solution, which has a great flaw - I assume no more than 3 codes per order.


    declare @T TABLE (
     ORDER_ID int,
     FS_CODE  varchar(8)
    );

    insert into @T(ORDER_ID,FS_CODE) values
    (100, 'AAA'),
    (200, 'AAA'),
    (200, 'BBB'),
    (300, 'AAA'),
    (300, 'BBB'),
    (300, 'ZZZ'),
    (333, 'AAA'),
    (334, 'AAA'),
    (400, 'BBB'),
    (400, 'AAA');

    ;WITH UNIQ_ORD as (
     select distinct
      ORDER_ID
     from @T

    )

    ,  ORD_CODE_RNUM as (
     select
      ORDER_ID
     , FS_CODE
     , ROW_NUMBER() over (partition by ORDER_ID order by ORDER_ID,FS_CODE) [ROWNUM]
     from @T
    )

    , FS_PER_COL as (
     select
      (select FS_CODE from ORD_CODE_RNUM C where C.ORDER_ID=UNIQ_ORD.ORDER_ID and ROWNUM=1) [FS1]
     , (select FS_CODE from ORD_CODE_RNUM C where C.ORDER_ID=UNIQ_ORD.ORDER_ID and ROWNUM=2) [FS2]
     , (select FS_CODE from ORD_CODE_RNUM C where C.ORDER_ID=UNIQ_ORD.ORDER_ID and ROWNUM=3) [FS3]
     from UNIQ_ORD
    )

    select
     COUNT(*) [CNT]
    , FS.FS1
    , FS.FS2
    , FS.FS3
    from FS_PER_COL FS
    group by
     FS.FS1
    , FS.FS2
    , FS.FS3

    the result is is

    CNT FS1 FS2 FS3
    3 AAA NULL NULL
    2 AAA BBB NULL
    1 AAA BBB ZZZ

    I'd like to have a code that would work if there are many more codes per order. Any suggestions would be appreciated.

  • Try this:
    CREATE TABLE #T (
        ORDER_ID int,
        FS_CODE varchar(8)
    );
    INSERT INTO #T(ORDER_ID, FS_CODE)
        VALUES    (100, 'AAA'),
                (200, 'AAA'),
                (200, 'BBB'),
                (300, 'AAA'),
                (300, 'BBB'),
                (300, 'ZZZ'),
                (333, 'AAA'),
                (334, 'AAA'),
                (400, 'BBB'),
                (400, 'AAA');

    WITH ORDERS_REWORKED AS (

        SELECT DISTINCT T.ORDER_ID, X.FS_CODES
        FROM #T AS T
            CROSS APPLY (
                VALUES (STUFF((
                            SELECT ', ' + T2.FS_CODE
                            FROM #T AS T2
                            WHERE T2.ORDER_ID = T.ORDER_ID
                            ORDER BY T2.FS_CODE
                            FOR XML PATH('')
                            ), 1, 2, '')
                        )
                ) AS X (FS_CODES)
    )
    SELECT ORW.FS_CODES, COUNT(DISTINCT ORW.ORDER_ID) AS ORDER_COUNT
    FROM ORDERS_REWORKED AS ORW
    GROUP BY ORW.FS_CODES
    ORDER BY ORW.FS_CODES;

    DROP TABLE #T;

  • Thank you!

  • migurus - Thursday, May 31, 2018 10:03 AM

    Thank you!

    You're welcome.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply