How to display result of multiple rows on one row in SQL?

Question

Sorry for my bad english.

I get some result.You can see in SS.

My query :

SELECT TransferOrderDate=Siparisler.datescheduled, 
       ProductTransferDate=UrunTransfer.daterealized, 
       FirmName=Bayi.firmname, 
       PlateOfTruck=UrunTransfer.plate, 
       DriverOfTruck=Surucu.name, 
       LatOfTruck=TankerKonum.lat, 
       LngOfTruck=TankerKonum.lng, 
       SpeedOfTruck=TankerKonum.speed, 
       LastUpdate=TankerKonum.readtime, 
       PrintID=UrunTransfer.printoutid 
FROM   producttransfer AS UrunTransfer 
       JOIN transferorder AS Siparisler 
         ON Siparisler.oid = UrunTransfer.transferorderid 
       JOIN dealer AS Bayi 
         ON Bayi.oid = Siparisler.dealerid 
       JOIN driver AS Surucu 
         ON Surucu.oid = Siparisler.driverid 
       JOIN devcocom_admin.tankerlocation AS TankerKonum 
         ON TankerKonum.tankerid = Siparisler.tankerid 
WHERE  UrunTransfer.daterealized > Dateadd(hour, -24, Getdate()) 
       AND TankerKonum.oid IN (SELECT Max(TankerKonum.oid) 
                               FROM   devcocom_admin.tankerlocation AS 
                                      TankerKonum 
                               GROUP  BY TankerKonum.tankerid)

Output like this :

enter image description here

But

This query result have 3 different dealer. But it have same truck,lat,lng,speed etc.

In reel one truck working for one or two or three dealer. I want to display all result in same row.

How can I change result of my query to be like this :

enter image description here

TDName1:TransferDate for FirmName1
TDName2:TransferDate for FirmName2
TDName3:TransferDate for FirmName3
PDName1 : ProductDate for FirmaName1
PDName2 : ProductDate for FirmaName2
PDName3 : ProductDate for FirmaName3

You can look into using PIVOT as described here http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql and here http://stackoverflow.com/questions/24470/sql-server-pivot-examples or http://stackoverflow.com/questions/9830960/how-to-pivot-table-with-t-sql — Kane, Nov 20 '13 at 22:01
Try to Use Common Table Expressions(CTE). Sample Example is here:[Click Here][1] [1]: http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Vahid Farahmandian, Dec 11 '13 at 09:42

score -2 · Answer 1 · answered Nov 20 '13 at 22:08

-2

Suppose you do what you want. What if some time 4, or more dealers appear? General solution is GROUP BY columns that are the same, and for the thing that you want to split to 3 columns I would go with some aggregate function that returns an array. In psql it's array_agg. So It would look like:

SELECT a,b,c
  , (array_agg(d order by a,b,c))[1] as d1
  , (array_agg(d order by a,b,c))[2] as d2
  , (array_agg(d order by a,b,c))[3] as d3
FROM foo
GROUP BY a,b,c

Good luck!

answered Nov 20 '13 at 22:08

murison

3,640
2
23
36

something like http://sqlfiddle.com/#!1/8d040/2/0 - but this uses array_agg function from psql. I don't know what is its name (if any) in ss2008. – murison Nov 21 '13 at 07:23

How to display result of multiple rows on one row in SQL?

1 Answers1