December 9, 2014 at 10:59 pm
This is my dataset,
batch_IddaerahKutipanfigure
---------------------------------------------------------------------------------------
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23NILAI3078
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23AGEN KUTIPAN647
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23REMBAU2882
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23TAMPIN3550
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23JELEBU2161
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23BACKDATED1
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23SEREMBAN15795
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23SEREMBAN 23
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23GEMAS2023
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23JEMPOL4208
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23KOPERASI5
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23KUALA PILAH4234
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23PORT DICKSON3485
*batch_Id is uniqueidentifier
How to write query to make it my result as following
batch_Id NILAI | AGEN KUTIPAN | REMBAU | TAMPIN
-------------------------------------------------------------------------------------------------
4308EF04-75C9-4AD5-ADCB-E7DB4816AA23 | 3078 | 647 | 2882 | 3550
Please help
December 10, 2014 at 2:00 am
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
December 30, 2014 at 11:43 am
I believe what you need is just dynamic pivot. see the below code.
CREATE TABLE TBL
(
batch_Id VARCHAR(36) NOT NULL
,daerahKutipan VARCHAR(25) NOT NULL
,figure INT NOT NULL
);
INSERT INTO TBL (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 );
declare @PivotColumns varchar(max);
select @PivotColumns=isnull(@PivotColumns+',','')+quotename(daerahKutipan) from (select distinct daerahKutipan from tbl) as t;
declare @strStr nvarchar(max)=N'
SELECT batch_id , @PivotColumns
FROM TBL
PIVOT
(
SUM(figure) FOR daerahKutipan IN (@PivotColumns)
)AS PIVT
';
SELECT @strStr=REPLACE(@strStr,'@PivotColumns',@PivotColumns)
EXEC sp_executesql @strStr
DROP TABLE TBL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply