SQLTeam.com | Weblogs | Forums

Help with concat data from same column

Hi gents,

I have a data that I need to concat data form one column separated by '," see below, what the best way to do this? thanks!

Blockquote

Blockquote

help us help you. please provide the data sample not an image

declare @sample table(national_id int, 
lastname varchar(50), 
first_name varchar(50), facility_name)

insert into @sample
select 222, 'John', 'test', 'Model'
declare @sample table(national_id int, 
lastname varchar(50), 
first_name varchar(50), 
facility_name varchar(50))

insert into @sample
select 222, 'John', 'test', 'Model' union
select 222, 'John', 'test', 'coco' 
SELECT 
   SS.national_id,lastname,first_name,
   STUFF((SELECT ',' +  US.facility_name 
          FROM @sample US
          WHERE US.national_id = SS.national_id
          FOR XML PATH('')), 1, 1, '') facility_name
FROM @sample SS
GROUP BY SS.national_id,lastname,first_name
ORDER BY 1

Thanks yosiasz . it did not let me load an excel file only jpg? how do I upload this data?

look at my previous post on sample data creation, it is a sql server script text, with DDL and DML
by you doing this, it gives us a window to your data without giving us access to your database server

declare @sample table(national_id int, 
lastname varchar(50), 
first_name varchar(50), 
facility_name varchar(50))

insert into @sample
select 222, 'John', 'test', 'Model' union
select 222, 'John', 'test', 'coco' 

Take a snapshot of the data

And load it as
jpg

I did.. its on my post.

Most people would want you to post your sample data not as an image but as consumable data in the form that I showed you

Ddl and dml. Anyways look at the query I sent you, should solve yoir problem

Oops my Bad Pasi

My misses understanding

I did not know

You don't have idea about what ddl dml statements are

Anyhow .. you got your solution :+1::+1:

Thanks.. this was hypothetical situation and I just needed to know how to query such a thing. what you had as ddl was good but I am still trying to build this from our huge dB with so many tables I need to query and cant provide the actual dldl, or mdl sorry.
Pasi

GOTCHA Pasi

:grin::grin:

thanks Harishgg1... I still have not figure it out..

Pasi

Can we have an offline discussion ? 

We can discuss and finish this off very quickly

Instead of endless back and forth and time energy 
waste

then provide us all of the tables or as much as you can?

No worries, I managed to find a way to resolve this. I had to select * into temp table and then query it using CTE.

Thanks again!