You must Sign In to post a response.
  • Category: SQL Server

    Column Values with Comma Separated

    Need integer column values, separated by comma..

    SUPPOSE Table name is Temp
    tempid statecode Active
    1 4 1
    2 5 1
    3 8 1
    4 7 0


    need output as :
    Statecode
    4,5,8
  • #769494
    You can use Concatenate option in SQL. Like below

    Example 1:
    select concat(concat(CampusId, ','),campusname) as aa from OrgCampus

    Example 2: I hope, this will work for you.

    select campusname +', '+ campusname +', '+campusname as aa from OrgCampus

    -----------------------------------------------------------------------------
    Regards,
    Gopi A.
    +91 9894315571
    Skype:gopi.net
    http://asaigopi-dotnet.blogspot.in/

  • #769558
    You can use the coalesce function of sql server to achieve this:

    Example:

    DECLARE @List VARCHAR(1000)
    select @List=
    coalesce(@List+',' , '' )+ cast(tempid as varchar(10))
    from Temp

    Select @list

    Thanks!
    Anjali Bansal

    ~Give your best and lead the world

  • #769559
    Hi

    Step 1
    ========
    create table temp
    (
    tempid int primary key identity(1,1),
    statecode int,
    Active int
    )

    Step 2
    ========
    insert into temp values(4,1),(5,1),(8,1),(7,0)

    statecode Active
    1 4 1
    2 5 1
    3 8 1
    4 7 0


    Step 3
    ========
    DECLARE @List VARCHAR(1000)
    select @List=coalesce(@List+',' , '' )+ cast(statecode as varchar(10))
    from Temp where Active=1
    Select @list


    --Your Output this
    4,5,8

    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #769746
    Declare @table table (tempid varchar(10),statecode varchar(10),Active varchar(10))
    insert into @table values(1, 4, 1)
    insert into @table values(2, 5, 1)
    insert into @table values(3, 8, 1)
    insert into @table values(4, 7, 0)
    select tempid +','+ statecode +','+ Active statecode from @table

  • #769754
    Hi Rajeshwari,

    You can refer the below code.

    SELECT t1.SNo, t1.Name, t1.ID
    Contents = REPLACE( (SELECT Content AS [data()]
    FROM mytable t2
    WHERE t2.ID = t1.ID
    ORDER BY Content
    FOR XML PATH('')
    ), ' ', ',')
    FROM mytable t1
    GROUP BY ID ;

    I hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"


  • Sign In to post your comments