SQLTeam.com | Weblogs | Forums

Add Extra Column After Dynamic Pivot Table

#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

#21

hi jacky

I tried to understand this

I think the problem is with the query
I mean
removing columns in blue will do the trick

removing columns in blue

0 Likes

#22

Hi I think this link will help understand how to do

Maybe somebody can interact with you live....it should resolve very quickly
Lot of experts on this forum..

:grinning::grinning:

0 Likes

#23

hello @harishgg1,
sorry for late reply. actually i think removing the query in Blue box that you mention will not solve the problem. I alse Need that record to be show in the query result.

0 Likes

#24

thank you @harishgg1, i will try it :grin::grin:

0 Likes

#25

hello @jeffw8713,
sorry for late reply. if T1 Value for 2019-03-21 is NULL then i want it to be 0,
So we still can COUNT the usage.

0 Likes