SQLTeam.com | Weblogs | Forums

Add Extra Column After Dynamic Pivot Table

#1

Hello, I want to add another extra column after Pivot, my current result is like this:
1

this is my current query:

DEClARE @startdate date
DECLARE @enddate date
SET @startdate = '2019-03-20'
SET @enddate = '2019-03-31'
CREATE table #tempdata(
	Name Varchar(100),
	date date,
	MeterSN Varchar(100),
	Reading float,
	RecordedBy Varchar(100),
	Remark Varchar(100)
)

INSERt INTO #tempdata SELECT c.Name, a.date, b.MeterSN, a.Reading, 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 @startdate AND @enddate
ORDER BY c.Name, a.date, b.MeterSN

DECLARE @col
 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,'')	
set @query = 'SELECT ROW_NUMBER() OVER( ORDER BY date) AS No, CONVERT(VARCHAR(10),date,120) AS Date,' + @cols + ', recordedby AS [Recorded By] , remark AS Remark from 
			 (
				select name,date, Reading, remark, RecordedBy
				from #tempdata 
			) x
			pivot 
			(
				MAX(Reading)
				for Name in (' + @cols + ')
			) p	ORDER by date '

execute(@query);
drop table #tempdata

i want to add another column after Area Column, the result should be like this

Usage records are come from Current Date Reading - Current Date -1 Reading

Here i prepared a 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',''
)

Please help.

0 Likes

#2

can you provide some readily consumable data? We don't have these tables (INF_Facility_ElectricalRecord, INF_Facility_ElectricalMeter,INF_Facility_Location) so we don't anything about them. We would need DDL, data and expected results

0 Likes

#3

hello @mike01,
i had provided that consumable table in the bottom of my query

0 Likes

#4

hi

you can do something like this

Pivot query
select col1,col2

Extra columns
select colabc,coldef

to get two together
put row number and join

select rownumber , col1,col2
join
select rownumber,colabc,coldef
on a.rownumber = b.rownumber

hope it helps
:slight_smile:
:slight_smile:

0 Likes

#5

hello @harishgg1,
can you explain more? I do not exactly understand.

0 Likes

#6

what i mean is !!!

put
pivot query
into temp table
-- pivot query ..... select col1,col2 into #abc from pivot query

other columns into another temp table
-- other query ... select colabc,coldef into #def from othercolumns query

now put row number

; with cte
as
(
select row_number() over() as rownumber , col1,col2
from #abc
) , cte2
as
(
select row_number() over() as rownumber , colabc,coldef
from #def
)
select a.col1,b.colabc,a.col2,b.coldef from cte a join cte2 b on
a.rownumber = b.rownumber

i hope this is clear

if not i can explain ever more very clearly
with sample data

:slight_smile:
:slight_smile:

0 Likes

#7

hello @harishgg1,
i get the point, but the problem is, the result of pivot table is dynamic.
i use something like this to get the column name.
"for Name in (' + @cols + ')",

how can i put the pivot result into temp table?

i will really appreciate it if you can explain more :slight_smile:

0 Likes

#8

Please see the above link..

0 Likes

#9

hi jacky

did my post help

i mean link i posted

???

0 Likes

#10

What you would need to do - to get multiple columns pivoted is to either add another pivot statement to the code or change the code to use a cross-tab construct.

I would provide an example - but the code you provided and the sample tables don't match up.

0 Likes

#11

hello @harishgg1,
Still trying. actually i still confuse about how to add Extra Column

0 Likes

#12

hello @jeffw8713,
Here is the sample tables:

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',''
)

0 Likes

#13

hi jacky

please help me understand
Usage records are come from Current Date Reading - Current Date -1 Reading
what does this mean ?
which table which column
how to get [ Current Date Reading - Current Date -1 Reading ]

i can write the query quickly
and give it to you

:slight_smile:
:slight_smile:

0 Likes

#14

This will get you 2 pivoted column values. You can display the columns in a report side by side - but in the pivot they will be grouped by each:

Declare @col1 nvarchar(max)
      , @col2 nvarchar(max)
      , @query nvarchar(max);

 Select @col1 = stuff(( Select ',' + quotename([Name])
                          From #tempdata
                         Group By
                               [Name]
                         Order By
                               [Name]
                           For xml Path(''), Type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
        
 Select @col2 = stuff(( Select ',' + quotename(concat([Name], 'Usage'))
                          From #tempdata
                         Group By
                               [Name]
                         Order By
                               [Name]
                           For xml Path(''), Type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

    Set @query = '
 Select row_number() over(order by date) As No
      , date 
      , ' + @col1 + '
      , ' + @col2 + '
      , recordedby As [Recorded By]
      , remark As Remark 
   From (Select t.[Name]
              , UsageName = concat(Name, ''Usage'')
              , date
              , Reading
              , remark
              , RecordedBy
              , Usage = Reading - lag(Reading, 1) over(Order By t.date)
           From #tempdata t
        ) x
  Pivot (max(Reading) For [Name] In (' + @col1 + ')) t     
  Pivot (max(Usage) For UsageName In (' + @col2 + ')) u     
  Order By
        date';
0 Likes

#15

Hello @harishgg1,

Here i will explain it:

i will use data on 2019-03-20 and 2019-03-21 as an example.
usage comes from T1 record on 2019-03-21 Minus T1 Record on 2019-03-20,
so it will become something like this Usage = 1416927 - 1406714 => 10213

0 Likes

#16

Hello @jeffw8713,
thanks for the query, its work, but there is a problem with the result

Current Result

What i Needed is Something Like This

so, the problem is the NULL record inside the result.

0 Likes

#17

hi jacky

please help me understand
how T2 T3 T4 T5 are coming
your diagram is like this

you create tempdata table like this

tempdata create SQL ..
INSERt INTO dbo.tempdata SELECT c.Name, a.date, b.MeterSN, a.Reading, 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 @startdate AND @enddate
ORDER BY c.Name, a.date, b.MeterSN

0 Likes

#18

i think this will solve the NULL issue

:crossed_fingers:
:crossed_fingers:

0 Likes

#19

If you don't have a T1 value for 2019-03-21 - what do you expect to be returned? The reason you have the NULL values is because there is no data at that level.

If your concern is where the columns are showing - that isn't really a problem.

0 Likes

#20

Hi @harishgg1,
as you see in my current Query. i Use Pivot To Make Row Become Column

0 Likes