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.FS3the result is is
CNT FS1 FS2 FS3
3 AAA NULL NULL
2 AAA BBB NULL
1 AAA BBB ZZZI'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;
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
Thank you!
- migurus - Thursday, May 31, 2018 10:03 AM
You're welcome.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply