Pivot Query SQL Duplicating rows

Question

I have a temporary table like this.

Then I'm pivoting that table using following query and output attached.

 SELECT *
 FROM (
  SELECT * from  @tmpTable2
 ) AS SourceTable
 PIVOT
(
max(timec)
FOR eventb
IN (
[Veh Reg Time],[Fitness ok time],[RFID Issue time],[MG-IN],[WB IN],
[MG-OUT],      [WB OUT]
)
) AS PivotTableAlias

In here issue is for a one 'RegNo' there is multiple records. But I want only one row for a one 'RegNo'. How can I achieve this?

Expected out put should be like this.

i think DISTINCT will help you OR GROUP BY

wiretext
– wiretext

2015-10-20 08:47:55 +00:00
Commented Oct 20, 2015 at 8:47 — wiretext
– wiretext, Commented Oct 20, 2015 at 8:47
How can I use distinct for this query?

Tom
– Tom

2015-10-20 08:51:36 +00:00
Commented Oct 20, 2015 at 8:51 — Tom
– Tom, Commented Oct 20, 2015 at 8:51
ok post your expected output result here

wiretext
– wiretext

2015-10-20 08:53:04 +00:00
Commented Oct 20, 2015 at 8:53 — wiretext
– wiretext, Commented Oct 20, 2015 at 8:53
expected result attached.

Tom
– Tom

2015-10-20 08:59:33 +00:00
Commented Oct 20, 2015 at 8:59 — Tom
– Tom, Commented Oct 20, 2015 at 8:59

Harish U · Accepted Answer · 2015-10-20 09:01:25Z

1

SELECT *
 FROM (
  SELECT regno,eventb,timec,rfid from  @tmpTable2
 ) AS SourceTable
 PIVOT
(
max(timec)
FOR eventb
IN (
[Veh Reg Time],[Fitness ok time],[RFID Issue time],[MG-IN],[WB IN],
[MG-OUT],      [WB OUT]
)
) AS PivotTableAlias

answered Oct 20, 2015 at 9:01

Harish U

891 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PLSQL_007 · Accepted Answer · 2015-10-20 09:13:41Z

0

SELECT pt.regno, pt.rfid, MAX(vrt), MAX(fot), MAX(rit), MAX(MI), MAX(wi), MAX(Mo), MAX(wo) FROM (
SELECT *
 FROM (
  SELECT * from @temtable2 t
 ) SourceTable
 PIVOT
(
max(timec)
FOR eventb
IN('Veh Reg Time' AS vrt,'Ftness Ok time' AS fot, 'RFID Issue time' AS rit, 'MG-IN' AS mi, 'WB IN' AS wi, 'MG-OUT' AS mo, 'WB OUT' AS wo)
) PivotTableAlias) pt
GROUP BY  pt.regno, pt.rfid

answered Oct 20, 2015 at 9:13

PLSQL_007

2132 silver badges8 bronze badges

Collectives™ on Stack Overflow

Pivot Query SQL Duplicating rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related