joining two different table

i have couple of tables that i am joining

organization table:

id, name

registration table:

id, name

after i execute this is how it looks

    org_id    org_name       reg_name         reg_id
--------------------------------------------------------
    329  abc          regname1       311    
    329  abc          regname2       298    

what i want to do is to display the data ONE row like this:

    org_id    org_name       reg_name                  reg_id
------------------------------开发者_开发技巧------------------------------------------
    329  abc          regname1;regname2          311;298    

note: my reg_name is dynamic, it might be one or ten.

I'd say give this post a try: comma separated list in SQL-server.

What you're looking for is a SQLCLR custom aggregator. You would then use your hand crafted custom aggregator in much the same way you'd use the SUM aggregator.

A rough-cut custom aggregator that will accumulate strings, sort them and then join with a semi-colon would looke like this:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1)]
public struct JoinString : IBinarySerialize
{
    List<String> strings;
    public void Init()
    {
        strings = new List<String>();
    }

    public void Accumulate(SqlString input)
    {
        strings.Add(input.Value);
    }

    public void Merge(JoinString Group)
    {
        strings.AddRange(Group.strings.ToArray());
    }

    public SqlString Terminate()
    {
        strings.Sort();
        return new SqlString(String.Join(";", strings.ToArray()));
    }


    public void Read(System.IO.BinaryReader r)
    {
        int items = r.ReadInt32();
        strings = new List<String>(items);
        for (var i = 0; i < items; i++)
            strings.Add(r.ReadString());
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(strings.Count);
        foreach (var s in strings)
            w.Write(s);
    }
}

In Oracle, you can use the list_aggr function also. For example:

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

Your query should like something:

SELECT a.org_id,a.org_nam, LISTAGG(reg_name, ';') WITHIN GROUP (ORDER BY a.org_id) AS reg_name, LISTAGG(reg_id, ';') WITHIN GROUP (ORDER BY a.org_id) AS reg_id
FROM   organization a , registration b
where b.org_id  = a.org_id
GROUP BY a.org_id,a.org_nam;