Link to home
Start Free TrialLog in
Avatar of Richard Cooper
Richard CooperFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Query

I have the following query which returns the booking for a data terminal. it returns a new row for each booking code.
I have to maniputlate this in a excel workbook to show one line per Works order and a new column for each booking code.
Is it possible to do this in the query?
current code
SELECT        WorkCentre, [Works Order No], [Unit Run Time], [Total Std Mins], ResId, [Actual Run Time], [Booking Code]
FROM            BSP_VAPSLabourPosting
WHERE        ([Works Order No] = '000000020136451')

Open in new window


returns

CS01      000000020136451      10      410.000000      CS01      805      
CS01      000000020136451      10      410.000000      CS01      688      BD
CS01      000000020136451      10      410.000000      CS01      5510      ES

is it possible to return the output for this code into one line for each Works Order so it returns

WCentre  WorksOrd                 UnitRun  TSTDMin   ResID     Run  BD  ES
CS01      000000020136451      10      410.000000      CS01      805      688  5510

I have just chosen one works order as an example.

Thanks in advance.
Avatar of Allan Nisbet
Allan Nisbet
Flag of United Kingdom of Great Britain and Northern Ireland image

Try something like this

SELECT k.id, GROUP_CONCAT(d.value)
  FROM keywords AS k
  INNER JOIN data as d ON k.id = d.id
  GROUP BY k.id

Open in new window


I would need the name of your tables and the name of your ID clumn to write it in full

Hope this helps
Avatar of John Tsioumpris
Essentially what you need is a PIVOT query ...check here for a start ..alternatively you could connect your data to an Access application and perform a CrossTab query which much easier...
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Cooper

ASKER

@Allan ,

 The table name is  BSP_VAPSLabourPosting its a view on several tables setup by the erp software providers.
columns are as the select statement. ID is the Works Order No

@John
   
 i am using VBA in excell to loop through and get info onto a single row per works order at the moment. i was just seeing if its possible to do it without the VBA.

Thanks
Thanks Vitor this is exactly what I was after.
CrossTab query in Access doesn't need VBA ....the only thing need is to perform a passthrough query to retrieve the data and then do the CrossTab...check for a tutorial here
Try This

SELECT k.WorkCentre, [Works Order No], [Unit Run Time], [Total Std Mins], ResId, [Actual Run Time], [Booking Code], GROUP_CONCAT(d.[Actual Run Time])
  FROM BSP_VAPSLabourPosting.WorkCentre AS k
  INNER JOIN BSP_VAPSLabourPosting as d ON k.WorkCentre = d.[Actual Run Time]
  GROUP BY k.WorkCentre
 WHERE ([Works Order No] = '000000020136451')

Open in new window


I think that should work
@Allan, this is SQL Server and not MySQL.
GROUP_CONCAT doesn't exist in SQL Server besides that the GROUP BY is malformed for SQL Server and the WHERE clause need to be placed before the GROUP BY.
thanks victor i could have sworn the tag was MySql when i started to answer it.