storeCode | carNo | storeName | remark | materialCode | materialName | dimension | unitName | qty | total | |
1 | 2026 | 5 | [공주]공암어린이집 | 1000840 | 냉동고구마(농우)중국산/맛탕용 | 1KG(55개-60개)유탕 | ea | 1.00 | 1-2 | |
2 | 2026 | 5 | [공주]공암어린이집 | 1000840 | 냉동고구마(농우)중국산/맛탕용 | 1KG(55개-60개)유탕 | ea | 3.00 | 3-1 | |
3 | 2026 | 5 | [공주]공암어린이집 | 1000840 | 냉동고구마(농우)중국산/맛탕용 | 1KG(55개-60개)유탕 | ea | 4.00 | 4-2 | |
4 | 2026 | 5 | [공주]공암어린이집 | 1000840 | 냉동고구마(농우)중국산/맛탕용 | 1KG(55개-60개)유탕 | ea | 2.00 | 2-1 | |
5 | 2026 | 5 | [공주]공암어린이집 | 1000840 | 냉동고구마(농우)중국산/맛탕용 | 1KG(55개-60개)유탕 | ea | 1.00 | ||
6 | 2026 | 5 | [공주]공암어린이집 | 1000840 | 냉동고구마(농우)중국산/맛탕용 | 1KG(55개-60개)유탕 | ea | 4.00 | ||
이런식으로 품목별로 집계를 하고 같은집계가 나오면 안보이게 하고싶습니다
WITH t (storeCode, carNo, storeName, remark, materialCode, materialName, dimension, unitName, qty) AS ( SELECT '2026', 5, '[공주]공암어린이집', '', '1000840 ', '냉동고구마(농우)중국산/맛탕용', '1KG(55개-60개)유탕', 'ea', 1.00 UNION ALL SELECT '2026', 5, '[공주]공암어린이집', '', '1000840 ', '냉동고구마(농우)중국산/맛탕용', '1KG(55개-60개)유탕', 'ea', 3.00 UNION ALL SELECT '2026', 5, '[공주]공암어린이집', '', '1000840 ', '냉동고구마(농우)중국산/맛탕용', '1KG(55개-60개)유탕', 'ea', 4.00 UNION ALL SELECT '2026', 5, '[공주]공암어린이집', '', '1000840 ', '냉동고구마(농우)중국산/맛탕용', '1KG(55개-60개)유탕', 'ea', 2.00 UNION ALL SELECT '2026', 5, '[공주]공암어린이집', '', '1000840 ', '냉동고구마(농우)중국산/맛탕용', '1KG(55개-60개)유탕', 'ea', 1.00 UNION ALL SELECT '2026', 5, '[공주]공암어린이집', '', '1000840 ', '냉동고구마(농우)중국산/맛탕용', '1KG(55개-60개)유탕', 'ea', 4.00 ) SELECT storeCode , carNo , storeName , remark , materialCode , materialName , dimension , unitName , qty , CASE WHEN ROW_NUMBER() OVER(PARTITION BY materialCode, qty ORDER BY (SELECT 1)) = 1 THEN CONCAT(CAST(qty AS INT), '-', COUNT(*) OVER(PARTITION BY materialCode, qty)) END total FROM t ;