Need help regarding sql query

[DeathFox]

---

I have this problem

Vehicle:
Motorcycle

Engine:
4-Stroke
V-Twin


Unson nako pag.gama na pag.retrieve kay maconcatenate kanang resulta sa Engine so the Result will be like this

Vehicle | Engine
Motorcycle | 4-Stroke, V-Twin

instead of

Vehicle | Engine
Motorcycle | 4-Stroke
Motorcycle | V-Twin

Thanks mga master

[dlanyer77]

can you post the complete attributes? it is remarkably simple but I need to be certain with the solution.

[RC211V]

look up "JOIN" statements

[DeathFox]

Vehicle has: VehicleID, Name
Engine has: EngineID, Name
VehicleEngine has: ID, VehicleID, EngineID
___

And any join statement will just produce this

Vehicle | Engine
Motorcycle | 4-Stroke
Motorcycle | V-Twin

[moz_k2]

use the string functions and alias

[sideon]

Try to use GROUP_CONCAT e.g.

SELECT vehicle, GROUP_CONCAT(engine) from vehicle_table GROUP BY vehicle

[DeathFox]

Sorry, MS SQL doesnt recognize that command. Any other ideas?

[sideon]

Sorry I didnt recognize that you are using MSSQL. That only works on Mysql5.
Anyway, heres the solution using MSSQL cursors

BEGIN
-- Start the cursor
DECLARE _cursor CURSOR
FOR
SELECT Vehicle, Engine
FROM VehicleTable
-- Open the cursor
OPEN _cursor
-- Declare Local Variables
DECLARE @Vehicle varchar(10)
DECLARE @Engine varchar(10)
-- Create temporary table
CREATE TABLE #Result (
Vehicle VARCHAR(10) NOT NULL,
Engine VARCHAR(8000) NULL
)
-- Load the first row into the cursor

FETCH NEXT FROM _cursor INTO @Vehicle, @Engine
-- check for final row
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2) -- check for error
BEGIN
IF EXISTS(SELECT * FROM #Result WHERE Vehicle = @Vehicle)
BEGIN
-- Row Exists so append result
UPDATE #Result
SET Engine = Engine + ',' + @Engine
WHERE Vehicle = @Vehicle
END
ELSE
BEGIN
-- Row didn't exist so new record
INSERT INTO #Result ( Vehicle, Engine )
VALUES ( @Vehicle, @Engine )
END
END
-- Load the next row into the cursor
FETCH NEXT FROM _cursor INTO @Vehicle, @Engine
END
CLOSE _cursor
DEALLOCATE _cursor
-- Show results
SELECT * FROM #Result
-- Clean up
DROP TABLE #Result
END

[jun_mar]

You need two things to achieve that:

First create a function that concatenates the values of the engine:

create function getEngineStr(@vehicleID as varchar(50)) --any name will do

returns varchar (800)

as

Begin

DECLARE @engine VARCHAR(8000)

SELECT @engine = COALESCE(@engine + ', ', '') + cast(EngineID as varchar)

FROM Engine

where vehicleID=@vehicleID

GROUP BY EngineID

return @engine

end


--Second , you do the query

Select Vehicle.Name,dbo.getEngineStr(Vehicle.vehicleid)as Engine from dbo.Vehicle

[moz_k2]

to make to make two columns into one
kani lang nga format gamita --> (table1.colA + ', ' + table2.colB) as newColumn

SELECT (table1.colA + ', ' + table2.colB) as newColumn, table1.colC, table3.colD
FROM ....the rest of the sql statement