Richard Cooper
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
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.
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')
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
I think that should work
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')
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.
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.
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