joining two different table

Question

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.

http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Damir Sudarevic, Jan 25 '11 at 15:42

score 0 · Answer 1 · edited Mar 27 '16 at 05:00

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;

Ralph Shillington · Answer 2 · 2011-01-25T16:04:15.320

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);
    }
}

This would work as long as order isn't important. If order is important the OP would need `XML PATH` — Martin Smith, Jan 25 '11 at 15:40

score 0 · Accepted Answer · answered Jan 25 '11 at 15:41

0

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

answered Jan 25 '11 at 15:41

TedOnTheNet

1,082
1
8
23

joining two different table

3 Answers3