How to retrive all row value in single column?

Question

Post reply

How to retrive all row value in single column?

Swamy Magam

Ten Centuries

Points: 1096
More actions
July 9, 2008 at 7:14 am

#189377

Hi
i have a table
tEmployee Column is Employeename
tEmployee
----------
Values
Employeename
--------------
scottt
Tiger
Huli
abrash
i want out put like
scott,tiger,huli,abrash in a single line
how to write select query for above output . please help.me.
Reagrds
swamy.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

Perhaps something like:

declare @test-2 table (aName varchar(10))

insert into @test-2

select 'scott' union all

select 'Tiger' union all

select 'Huli' union all

select 'abrash'

--select * from @test-2

declare @accum varchar(max) set @accum = ''

select

@accum

= case when @accum <> ''

then @accum + ',' + aName

else aName

end

from @test-2

select @accum as [@accum]

/* -------- Sample Output: --------

@accum

--------------------------

scott,Tiger,Huli,abrash

*/

Swamy Magam Ten Centuries Points: 1096 More actions · Answer 2

Thank u.

Actually i donot know the rows in the temployee table

Hi

i have a table

tEmployee Column is Employeename

tEmployee

----------

Values

Employeename

--------------

scottt

Tiger

Huli

abrash

...

....

.....

upto end of rows

actually

select 'scott' union all

select 'Tiger' union all

select 'Huli' union all

select 'abrash'

above values are fixed but i want from temployee table row values.

Ragards

Swamy.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

I am seeing this done a lot using xml path. I have not done a lot of tests, but I hear it is more efficient.

declare @test-2 table (aName varchar(10))

insert into @test-2

select 'scott' union all

select 'Tiger' union all

select 'Huli' union all

select 'abrash'

--select * from @test-2

declare @accum varchar(max)

Select @accum= Stuff((

select ',' +

aName

from @test-2

for xml path('')

),1,1,'')

select @accum as [@accum]

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 4

Swamy m (7/9/2008)
Thank u.
Actually i donot know the rows in the temployee table

It's just a way of showing that it actually works... the temporary table is just for demonstration purposes. You will need to change the code to use your real table name and not use the temporary table at all.

Please see the link in my signature line for more information... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 5

HI,

On what basis you want to concatinate? if ou have one Million records, you dont want to concatinate all the rows into one single column!!!.

So what is your actual requirment?

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 6

Now, there's the 64 thousand dollar question! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 7

How about COALESCE

Declare @vc as varchar(max)

Select @vc = COALESCE(@vc + ',','') + EmployeeName from tEmployee

Select @vc

Also, you can use where clause to filter the Select statament

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Swamy Magam Ten Centuries Points: 1096 More actions · Answer 8

Thank u So much . it is working.:cool:

Declare @vc as varchar(max)

Select @vc = COALESCE(@vc + ',','') + EmployeeName from tEmployee

Select @vc

Regards

swamy.