i want sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

i want sql query

Author

Topic

poornimahc
Starting Member

2 Posts

Posted - 2009-02-02 : 11:24:15

i hav one table with field name(varchar255) it contains records

after select * from table;

i will get name
-----
poornima
pallavi
avinash
putta
etc

but i want output should be in sinle row like
poornima,pallavi,avi,putta
like i want query for this

poornimahc

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-02 : 11:30:53

If you want the output in seperate columns do a search on this site for CROSS TAB

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-02 : 11:33:37

try this

declare @tab table ( name varchar(50))
insert into @tab
select 'poornima' union all
select 'pallavi' union all
select 'avinash' union all
select 'putta'

select stuff(( select ','+name from @tab for xml path ('')),1,1,'') as name

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-02 : 16:08:32

Try using this.

DECLARE @NAME AS CHAR(1000)
DECLARE @COUNT AS INT
SELECT @COUNT = 1
SELECT @NAME = ''
WHILE @COUNT <= (SELECT COUNT(*) FROM TABLE)
BEGIN
SELECT @NAME = RTRIM(@NAME) + (SELECT NAME FROM TABLE WHERE ROWID = @COUNT) + ','
SELECT @COUNT = @COUNT + 1
END
SELECT @NAME

This can be used if you have a rowid or a sequence number in your table(UNIQUE KEY). If not, then create a staging table with identity column and run this query against that.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-02 : 16:33:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118937

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources