SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Combining Multiple Rows into One Row – Sql Server

on January 11, 2012

In this example i am going to describe how to combine multiple  rows in one column in MS SQL.

Here is the scenario
uSER_id and respective Hobbies are listed in the table.

i want to combine all the hobbies of a user

declare @User table
(
UserId int,
Hobbies VARCHAR(100)
)

INSERT INTO @User (UserId,Hobbies) values
(1, ‘gaming’) , (1, ‘cricket’),
(2, ‘movies’) , (2, ‘cricket’), (2, ‘gardening’) , (2, ‘football’),
(3, ‘gaming’) , (3, ‘football’)
SELECT * FROM @User

combine multiple rows

SELECT DISTINCT
UserId,
Allhobbies = substring( ( SELECT ‘, ‘ + Hobbies
FROM @User T1
WHERE T1.UserId = T2.UserId FOR XML path(”), elements
),2,500)
FROM @User T2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: