របៀប Select Max AutoNumber ដោយមានលក្ខ័ណច្រើនមកបង្ហាញក្នុង Row តែមួយ

របៀប​ Select Max AutoNumber  ដោយមានលក្ខ័ណច្រើនមកបង្ហាញក្នុង Row តែមួយ

-- 1 Create Temporary Table for store data --

      Create table #tbltmp(ProvinceCode nvarchar(10),CustomerID nvarchar(15),

                                      CustomerCode nvarchar(3),AutoNumber int)

 

-- 2 Select data from table for insert into Temporary Table -----

Insert Into #tbltmp

Select Province,CustomerID,

LEFT(CustomerID,3) AS 'CustomerCode',

MAX(CAST(SUBSTRING(CustomerID,6, LEN(CustomerID)) AS INT)) AS     

        AutoNumber 

From tab_Ar_Customer 

Where Active='Y' 

Group BY Province,CustomerID

Order By Province,CustomerID

-- 3 Select Max AutoNumber CustomerID by each ProvinceCode from table Temporary--

Select 

MAX(Case When ProvinceCode='BTM' Then AutoNumber End) AS BTM,

MAX(Case When ProvinceCode='BTB' Then AutoNumber End) AS BTB,

MAX(Case When ProvinceCode='KPC' Then AutoNumber End) AS KPC,

MAX(Case When ProvinceCode='KPCH' Then AutoNumber End) AS KPCH,

MAX(Case When ProvinceCode='KPSP' Then AutoNumber End) AS KPSP,

MAX(Case When ProvinceCode='KPT' Then AutoNumber End) AS KPT,

MAX(Case When ProvinceCode='KP' Then AutoNumber End) AS KP,

MAX(Case When ProvinceCode='KD' Then AutoNumber End) AS KD,

MAX(Case When ProvinceCode='KK' Then AutoNumber End) AS KK,

MAX(Case When ProvinceCode='KT' Then AutoNumber End) AS KT,

MAX(Case When ProvinceCode='M.KIRY' Then AutoNumber End) AS 'M.KIRY',

MAX(Case When ProvinceCode='PP' Then AutoNumber End) AS PP,

MAX(Case When ProvinceCode='PVH' Then AutoNumber End) AS PVH,

MAX(Case When ProvinceCode='PV' Then AutoNumber End) AS PV,

MAX(Case When ProvinceCode='PS' Then AutoNumber End) AS PS,

MAX(Case When ProvinceCode='RKR' Then AutoNumber End) AS RKR,

MAX(Case When ProvinceCode='SR' Then AutoNumber End) AS SR,

MAX(Case When ProvinceCode='SHN' Then AutoNumber End) AS SHN,

MAX(Case When ProvinceCode='ST-TR' Then AutoNumber End) AS 'ST-TR',

MAX(Case When ProvinceCode='SVR' Then AutoNumber End) AS SVR,

MAX(Case When ProvinceCode='TK' Then AutoNumber End) AS TK,

MAX(Case When ProvinceCode='Kep' Then AutoNumber End) AS Kep,

MAX(Case When ProvinceCode='PL' Then AutoNumber End) AS PL,

MAX(Case When ProvinceCode='ODMC' Then AutoNumber End) AS ODMC,

MAX(Case When ProvinceCode='TBK' Then AutoNumber End) AS TBK

From #tbltmp

4- Result: