CODEWITHSUNDEEP
sqlsql-server
user5916784
user5916784

Reputation:

sql server column value to be converted in comma seperated

Before this question is marked as duplicate, i know how it can be done but without doing a declare statement i want to do it within a query itself

like i have this query 

select distinct costcenterid,costcentername,costcenterdesc,contactid,expirationdate,portal_id,
    active,customername,branchid,id from costcenter cc
    inner join branchesinportals bp on bp.portalid = cc.portal_id

the branchid and the id fields have different values but all other rows have same values so if i remove those and do a distinct it works good, i get one record

i want that it should always return me one record and combine the columns branchid and id as a comma separated values

i tried looking a this link which seems to be working but how can i integrate that link code with query

http://www.codeproject.com/Tips/635166/SQL-Column-Values-as-Comma-Separated-String

Upvotes: 2

Views: 41

Answers (1)

Raj More
Raj More

Reputation: 48018

You can use FOR XML to solve this problem. Here is a list of column names (you can run it in any SQL Server Database):

Select Stuff((
    Select ', ' + cast(COLUMN_NAME as varchar(max))
    From INFORMATION_SCHEMA.COLUMNS
    For XML PATH('')
), 1, 2, '');

Here is how to have a one-to-many value set show up:

Select Distinct C1.TABLE_NAME,
    Stuff((
        Select ', ' + Cast(COLUMN_NAME as VarChar (Max))
        From INFORMATION_SCHEMA.COLUMNS C2
        Where C1.TABLE_NAME = C2.TABLE_NAME
        For Xml Path ('')
    ), 1, 2, '') Columns
From INFORMATION_SCHEMA.COLUMNS C1

Here is the output from my master database tables and columns:

enter image description here

Upvotes: 2

Related Questions