Quick cross-tab solution (hard coded)

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#BATCH') IS NOT NULL DROP TABLE #BATCH;

CREATE TABLE #BATCH

(

batch_Id VARCHAR(36) NOT NULL

,daerahKutipan VARCHAR(25) NOT NULL

,figure INT NOT NULL

);

INSERT INTO #BATCH (batch_id,daerahKutipan,figure)

VALUES

('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','NILAI' ,3078 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','AGEN KUTIPAN' ,647 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','REMBAU' ,2882 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','TAMPIN' ,3550 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','JELEBU' ,2161 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','BACKDATED' ,1 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','SEREMBAN' ,15795)

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','SEREMBAN 2' ,3 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','GEMAS' ,2023 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','JEMPOL' ,4208 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','KOPERASI' ,5 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','KUALA PILAH' ,4234 )

,('4308EF04-75C9-4AD5-ADCB-E7DB4816AA23','PORT DICKSON' ,3485 );

SELECT

B.batch_Id

,MAX(CASE WHEN B.daerahKutipan = 'NILAI' THEN B.figure END) AS [NILAI]

,MAX(CASE WHEN B.daerahKutipan = 'AGEN KUTIPAN' THEN B.figure END) AS [AGEN KUTIPAN]

,MAX(CASE WHEN B.daerahKutipan = 'REMBAU' THEN B.figure END) AS [REMBAU]

,MAX(CASE WHEN B.daerahKutipan = 'TAMPIN' THEN B.figure END) AS [TAMPIN]

,MAX(CASE WHEN B.daerahKutipan = 'JELEBU' THEN B.figure END) AS [JELEBU]

,MAX(CASE WHEN B.daerahKutipan = 'BACKDATED' THEN B.figure END) AS [BACKDATED]

,MAX(CASE WHEN B.daerahKutipan = 'SEREMBAN' THEN B.figure END) AS [SEREMBAN]

,MAX(CASE WHEN B.daerahKutipan = 'SEREMBAN 2' THEN B.figure END) AS [SEREMBAN 2]

,MAX(CASE WHEN B.daerahKutipan = 'GEMAS' THEN B.figure END) AS [GEMAS]

,MAX(CASE WHEN B.daerahKutipan = 'JEMPOL' THEN B.figure END) AS [JEMPOL]

,MAX(CASE WHEN B.daerahKutipan = 'KOPERASI' THEN B.figure END) AS [KOPERASI]

,MAX(CASE WHEN B.daerahKutipan = 'KUALA PILAH' THEN B.figure END) AS [KUALA PILAH]

,MAX(CASE WHEN B.daerahKutipan = 'PORT DICKSON' THEN B.figure END) AS [PORT DICKSON]

FROM #BATCH B

GROUP BY B.batch_Id;

Results

batch_Id NILAI AGEN KUTIPAN REMBAU TAMPIN JELEBU BACKDATED SEREMBAN SEREMBAN 2 GEMAS JEMPOL KOPERASI KUALA PILAH PORT DICKSON

------------------------------------ ----------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------

4308EF04-75C9-4AD5-ADCB-E7DB4816AA23 3078 647 2882 3550 2161 1 15795 3 2023 4208 5 4234 3485