Consulta en SQL

[Libras]

Código SQL:

CREATE TABLE operacion
(
id INT,
nombre VARCHAR(20),
correlativo INT
)
 
INSERT INTO operacion VALUES (1, 'CAMISA', 3)
INSERT INTO operacion VALUES (2, 'PANTALON', 4)
 
 
CREATE TABLE detalle
(
id INT,
op INT,
correlativo INT
)
 
INSERT INTO detalle VALUES (1, 444, 3)
INSERT INTO detalle VALUES (2, 445, 3)
 
 
 
 
 
ALTER FUNCTION VALUE2 (@id INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @DepartmentName VARCHAR(1000) 
    SELECT @DepartmentName = COALESCE(@DepartmentName,'') + CONVERT(VARCHAR(20),op) + ','  
    FROM detalle WHERE correlativo=@id
    RETURN @DepartmentName
END
 
SELECT t1.id,nombre,(SELECT dbo.VALUE2(t1.correlativo)) FROM operacion AS t1
LEFT JOIN detalle AS t2 ON (t1.correlativo=t2.correlativo)
GROUP BY t1.id,t1.nombre,t1.correlativo
 

Resultado:

1 CAMISA 444,445,
2 PANTALON NULL