Re: Query to combine two 'many:one' tables

Hi Jens,

Thanks for the reply. When I tried Uri Dimant's code (below) I got a bunch of errors - did you get it working?

Cheers,

JON ERRORS:

        Server: Msg 111, Level 15, State 1, Line 12
        'CREATE FUNCTION' must be the first statement in a query batch.
        Server: Msg 137, Level 15, State 1, Line 18
        Must declare the variable '_at_COL'.
        Server: Msg 178, Level 15, State 1, Line 21
        A RETURN statement with a return value cannot be used in this

Uri Dimant's CODE:

        CREATE TABLE Sometable (col1 INTEGER, col2 CHAR(1), PRIMARY KEY (col1,col2))

        INSERT INTO Sometable VALUES (1, 'A')
        INSERT INTO Sometable VALUES (1, 'B')
        INSERT INTO Sometable VALUES (1, 'C')
        INSERT INTO Sometable VALUES (2, 'D')
        INSERT INTO Sometable VALUES (2, 'E')
        INSERT INTO Sometable VALUES (2, 'F')

        CREATE FUNCTION fn_Myfunc (_at_COL int)
        RETURNS VARCHAR(30)
        AS
        BEGIN
        DECLARE _at_W VARCHAR(30)
        SET _at_W=''
        SELECT _at_W=_at_W+col2+',' FROM Sometable WHERE col1=_at_COL
        SET _at_W= LEFT(@W,LEN(@W)-1)
        RETURN _at_W
        END

        SELECT  col1,dbo.fn_Myfunc(col1)
        FROM
        (
         SELECT DISTINCT col1 FROM  Sometable
        )AS DER

        DROP TABLE Sometable