How to get comma separated data from 1 table and get related data from another table?

I have 1 table in which category are stored. e.g:

CategoryId      CategoryName
-------------------------------
1              General

2              dummy

3              test

now i have 1 another table in which i store the articles and in that table i store articles id with comma separated values,as 1 article can be in more than 1 category

ArticleID       Content       CategoryId
-------------------------------------------
1              Test            1,2
2              Test2           1,3
3              Test3           1,2,3
4              Test4           2

Now at front side when i display the article i want to display article categoryname al开发者_开发知识库so.

So can you tell me how can i achieve this???

Output should be like::

ArticleId      Content        Category
------------------------------------------
1             Test             General,dummy
2             Test2            General,test
3             Test3            General,dummy,test
4             Test4            dummy

Maybe you should consider changing your data model.

Create an extra table Article_Category (ArticleID, CategoryID), and for each article put in a row for each category the article belongs to.

The query then becomes a simple join of the three tables. Sort the output by article and category, and you can easily "flatten" the output in the GUI.

In MySQL, you can do this with the GROUP_CONCAT aggregate function. I don't think there is something like this in T-SQL, but you could use it in your search. Here is an example of someone asking about it: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f09d4166-2030-41fe-b86e-392fbc94db53.

Good luck!

If you know some .NET you can create something like a StringAggregator. You're previous questions indicate you do know some programming.

Create a SQL Server 2005/2008 project and use this code

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Collections.Generic
Imports System.IO

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize:=8000)> _
Public Structure StringAggrComma
    Implements IBinarySerialize

    Private values As List(Of String)

    Public Sub Init()
        ' Put your code here
        values = New List(Of String)
    End Sub

    Public Sub Accumulate(ByVal value As SqlString)
        ' Put your code here
        values.Add(value.Value.Trim)
    End Sub

    Public Sub Merge(ByVal value As StringAggrComma)
        ' Put your code here
        For Each val As String In value.values
            Accumulate(val)
        Next
    End Sub

    Public Function Terminate() As SqlString
        ' Put your code here
        Return New SqlString(String.Join(", ", values.ToArray))
    End Function

    Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
        w.Write(values.Count)
        For Each item As String In values
            w.Write(item)
        Next
    End Sub

    Public Sub Read(ByVal r As System.IO.BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
        values = New List(Of String)
        For i As Int32 = 0 To r.ReadInt32
            values.Add(r.ReadString)
        Next
    End Sub
End Structure