How to shorten the my statement for 100 records different of Nation_code and Description.
Example:-
INSERT INTO @SHY_DT
SELECT Description,
ISNULL(COUNT(Quantity),0),
ISNULL(SUM(Quantity),0)
FROM M_Master
WHERE
scode = @BMSCode
AND
Nation_Code = Ncode
My Table
Ncode Description
MYS Malaysia
SGP Singapore
CHN China
......
What I have tried:
Currently, My statement as below
INSERT INTO @SHY_DT
SELECT Description,
ISNULL(COUNT(Quantity),0),
ISNULL(SUM(Quantity),0)
FROM M_Master
WHERE
scode = @BMSCode
AND
Nation_Code = 'MYS'
;
INSERT INTO @SHY_DT
SELECT 'MALAYSIA',
ISNULL(COUNT(Quantity),0),
ISNULL(SUM(Quantity),0)
FROM M_Master
WHERE
scode = @BMSCode
AND
Nation_Code = 'MYS'
;
INSERT INTO @SHY_DT
SELECT 'SINGAPORE',
ISNULL(COUNT(Quantity),0),
ISNULL(SUM(Quantity),0)
FROM M_Master
WHERE
s_code = @BMSCode
AND
Nation_Code = 'SGP'
;
INSERT INTO @SHY_DT
SELECT.....