CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot-table
Tom
Tom

Reputation: 1373

Pivot Query SQL Duplicating rows

I have a temporary table like this. enter image description here

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

enter image description here

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. enter image description here

Upvotes: 0

Views: 70

Answers (2)

PLSQL_007
PLSQL_007

Reputation: 213

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

Upvotes: 0

Harish U
Harish U

Reputation: 89

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

Upvotes: 1

Related Questions