SELECT MAX(A.TYPE_ID) AS N'NHÓM CẤP 1', MAX(A.GROUP_CODE) AS N'MÃ NHÓM VẬT LIỆU', MAX(A.GROUP_NAME) AS N'TÊN NHÓM VẬT LIỆU',
MAX(B.GROUP_CODE) AS N'MÃ NHÓM CHA', MAX(B.GROUP_NAME) AS N' NHÓM VẬT LIỆU CHA',MAX(A.UNIT_ID) AS N'ĐƠN VỊ TÍNH', MAX(A.IS_LEAF) AS IS_LEAF, 
MAX(A.GROUP_LEVEL) AS LEVEL, MAX(A.EXP_ACCTNO) AS EXP_ACCTNO,
MAX(A.INC_ACCTNO) AS  INC_ACCTNO, MAX(A.LIQ_ACCTNO) AS LIQ_ACCTNO, MAX(A.MATERIAL_ACCTNO) AS MATERIAL_ACCTNO,
MAX(A.MATERIAL_ACCTNO_NHNN) AS MATERIAL_ACCTNO_NHNN, MAX(A.VAT_ACCTNO) AS VAT_ACCTNO, MAX(A.NOTES) AS N'GHI CHÚ'
FROM MW_GROUP A
LEFT JOIN MW_GROUP B ON A.PARENT_ID = B.GROUP_ID
GROUP BY A.GROUP_CODE
HAVING COUNT (*) =1
ORDER BY MAX(A.TYPE_ID) DESC, MAX(A.GROUP_LEVEL)
 
SELECT MAX(A.MATERIAL_CODE) AS N' MÃ LOẠI VẬT LIỆU',
MAX(A.MATERIAL_NAME) AS N' TÊN LOẠI VẬT LIỆU',
MAX(B.GROUP_CODE) AS N'MÃ NHÓM',
MAX(B.GROUP_NAME) AS N'TÊN NHÓM',
ISNULL(MAX(U.UNIT_NAME),'') AS N'ĐƠN VỊ TÍNH',
MAX(A.EXP_ACCTNO) AS N'TÀI KHOẢN CHI PHÍ',
MAX(A.MATERIAL_ACCTNO) AS N'TÀI KHOẢN VẬT LIỆU',
MAX(A.VAT_ACCTNO) AS N'TÀI KHOẢN VAT',
ISNULL(MAX(A.INC_ACCTNO),'') AS N'TÀI KHOẢN GL TREO',
ISNULL(MAX(A.LIQ_ACCTNO),'') AS N'TÀI KHOẢN CHI PHÍ THANH LÝ',
ISNULL(MAX(A.MATERIAL_ACCTNO_NHNN),'') AS N'TÀI KHOẢN THU NHẬP THANH LÝ '
FROM MW_MATERIAL A
INNER JOIN MW_GROUP B ON A.GROUP_ID = B.GROUP_ID
LEFT JOIN CM_UNIT U ON A.UNIT_ID = U.UNIT_ID
GROUP BY A.MATERIAL_CODE
HAVING COUNT (*) =1