Friday, 12 July 2013

select Group_by Comma Separated

Hi ,
         In this article we will see how to get a group by comma separated values sql server.

Check Below example ::

step1:: Create table and fill some data in that table.


Create table #Group_by_CommaSeprated(id int ,Hosp_name varchar(50),City varchar(50))
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'sandip','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'raj','pune')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'ajit','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'aditya','nashik')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rachit','bhopal')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rakesh','bihar')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'shrikant','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'chandan','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'dishal','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'saket','UP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'pranav','sangli')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'aditya','kolhapur')

step 2:: Check your data .

select * from #Group_by_CommaSeprated


Step 3:: Select Query for get a group by comma separated values sql server.


SELECT id, Hosp_name =
STUFF((SELECT ', ' + Hosp_name
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, ''), City =
STUFF((SELECT ', ' + City
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM #Group_by_CommaSeprated a
GROUP BY id

Step 4:: OUTPUT



I hope You got something useful in this article. I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.


Related Posts

Group by in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

No comments:

Post a Comment

8451831077