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