Null Value in Multiple Dynamic Pivot

1

Hello, i want to use multiple pivot for dynamic column,

here is my consumable table

CREATE TABLE INF_Facility_ElectricalRecord (
id int primary key identity(1,1),
date date,
CurrentMeterSNID int,
Reading float,
recordedby varchar(100),
remark varchar(100)
)
Create TABLE INF_Facility_ElectricalMeter
(
id int primary key identity(1,1),
MeterSN varchar(100),
LocationId int,
date date
)
CREATE TABLE INF_Facility_Location
(
id int primary key identity(1,1),
Name varchar(100),
Description Varchar(100)
)

INSERT INTO INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-20',1,1234,'',''
)
INSERT INTO INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-21',2,2345,'',''
)
INSERT INTO INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-22',3,3456,'',''
)
INSERT INTO INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-23',4,4567,'',''
)
INSERT INTO INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-24',5,5678,'',''
)

INSERT INTO INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T1001', 1, GETDATE()
)
INSERT INTO INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T2001', 2, GETDATE()
)
INSERT INTO INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T3001', 3, GETDATE()
)
INSERT INTO INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T4001', 4, GETDATE()
)
INSERT INTO INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T5001', 5, GETDATE()
)
INSERT INTO INF_Facility_Location(Name, Description) VALUES(
'T1',''
)
INSERT INTO INF_Facility_Location(Name, Description) VALUES(
'T2',''
)
INSERT INTO INF_Facility_Location(Name, Description) VALUES(
'T3',''
)
INSERT INTO INF_Facility_Location(Name, Description) VALUES(
'T4',''
)
INSERT INTO INF_Facility_Location(Name, Description) VALUES(
'T5',''
)

My Current Query

CREATE table #tempdata(
	Name Varchar(100),
	date date,
	MeterSN Varchar(100),
	Reading float,
	Usage VARCHAR(20),
	UsageValue float,
	RecordedBy Varchar(100),
	Remark Varchar(100)
)

INSERT INTO #tempdata 
	SELECT	c.Name, a.date, b.MeterSN, a.Reading, c.Name + ' Usage' AS Usage, 
			Reading - LAG(reading) over(partition By currentMeterSNID order by a.date) as UsageValue,  
			a.RecordedBy, a.Remark 
				FROM INF_Facility_ElectricalRecord a
					INNER JOIN INF_Facility_ElectricalMeter b ON b.id = a.currentMeterSNID
						INNER JOIN INF_Facility_Location c ON c.id = b.LocationID
							WHERE a.date BETWEEN '2019-03-20' AND '2019-06-04'
								ORDER BY c.Name, a.date, b.MeterSN


DECLARE @cols AS NVARCHAR(MAX),
		@cols2 AS NVARCHAR(MAX),
		@query  AS NVARCHAR(MAX)

	SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
						from #tempdata
						group by Name
						order by Name
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	SELECT @cols2 = STUFF((SELECT ',' + QUOTENAME(Usage) 
						from #tempdata
						group by Usage
						order by Usage
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')

	set @query = 'SELECT ROW_NUMBER() OVER( ORDER BY date) AS No, CONVERT(VARCHAR(10),date,120) AS Date,' + @cols + ','+ @cols2 + ', recordedby AS [Recorded By] , remark AS Remark from 
				 (
					select name,date, Reading, Usage, UsageValue, remark, RecordedBy
					from #tempdata 
				) x
				pivot 
				(
					MAX(Reading)
					for Name in (' + @cols + ')
				) p	
				pivot 
				(
					MAX(UsageValue)
					for Usage in (' + @cols2 + ')
				) p
				ORDER by date '

	execute(@query);
	drop table #tempdata

My Current Result

Capture
Capture865×262 8.56 KB

The Problem is, i want to merge all Column Base on date, so there will no NULL Value in the column

2

you have to manipulate the string a little bit. I also added missing records to get results

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

CREATE table #tempdata(
Name Varchar(100),
date date,
MeterSN Varchar(100),
Reading float,
Usage VARCHAR(20),
UsageValue float,
RecordedBy Varchar(100),
Remark Varchar(100)

)

INSERT INTO #tempdata
SELECT c.Name, a.date, b.MeterSN, a.Reading, c.Name + ' Usage' AS Usage,
Reading - LAG(reading) over(partition By currentMeterSNID order by a.date) as UsageValue,
a.RecordedBy, a.Remark
FROM INF_Facility_ElectricalRecord a
INNER JOIN INF_Facility_ElectricalMeter b ON b.id = a.currentMeterSNID
INNER JOIN INF_Facility_Location c ON c.id = b.LocationID
WHERE a.date BETWEEN '2019-03-20' AND '2019-06-04'
ORDER BY c.Name, a.date, b.MeterSN

/* add data for all days */
;WITH CTE AS (SELECT 'T1'as name, 10 as increment union all
select 'T2' , 20 UNION ALL
select 'T3',30 UNION ALL
select 'T4',40 UNION ALL
select 'T5', 50 )

insert into #tempdata
SELECT c.Name, c.date, c.MeterSN, c.Reading, c.Usage, null, c.RecordedBy, c.Remark
FROM (
SELECT C.NAME, T.DATE, T.METERsn +cast(increment as varchar(10)) MeterSN, T.Reading + increment as Reading, c.name + ' Usage' as Usage, t.RecordedBy, t.remark
FROM CTE C
CROSS APPLY #TEMPDATA T) C
left JOIN #TEMPDATA T
ON C.NAME = T.NAME
and c.date = t.date
where t.date is null

DECLARE @cols AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@MaxCols nvarchar(max) = '',
@MaxCols2 nvarchar(max) = ''

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
					from #tempdata
					group by Name
					order by Name
			FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)') 
		,1,1,''),
		 @cols2 = STUFF((SELECT ',' + QUOTENAME(Usage) 
					from #tempdata
					group by Usage
					order by Usage
			FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)') 
		,1,1,'')
		,@Maxcols = STUFF((SELECT ',Max(' + QUOTENAME(Name) + ') as ' + QUOTENAME(Name)
					from #tempdata
					group by Name
					order by Name
			FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)') 
		,1,1,'')
		 ,@Maxcols2 = STUFF((SELECT ',Max(' + QUOTENAME(Usage)  + ') as ' + QUOTENAME(Usage)
					from #tempdata
					group by Usage
					order by Usage
			FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)') 
		,1,1,'')

set @query = 'SELECT ROW_NUMBER() OVER( ORDER BY date) AS No, CONVERT(VARCHAR(10),date,120) AS Date,' + @Maxcols + ','+ @Maxcols2 + ', recordedby AS [Recorded By] , remark AS Remark from 
			 (
				select name,date, Reading, Usage, UsageValue, remark, RecordedBy
				from #tempdata 
			) x
			pivot 
			(
				MAX(Reading)
				for Name in (' + @cols + ')
			) p	
			pivot 
			(
				MAX(Usage)
				for Usage in (' + @cols2 + ')
			) p
			Group by CONVERT(VARCHAR(10),date,120), recordedby , remark, date
			ORDER by date '

execute(@query);