convert multiple rows ,multiple columns to a singl

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

SQL Server 2005 Forums

Transact-SQL (2005)

convert multiple rows ,multiple columns to a singl

Author

Topic

ramesh31
Starting Member

11 Posts

Posted - 2007-10-11 : 20:28:57

convert multiple rows ,multiple columns to a single row

table1

col1 col2 col3 col4
----- ---- ---- -----

86365.23 NULL 839 251
14123.78 NULL 840 251
NULL 58392.56 843 251
NULL 103615.49 844 251
2387.79 NULL 848 251

i want the output in single row as

86365.23 NULL 839 251 14123.78 NULL 840 251
NULL 58392.56 843 251 NULL 103615.49 844 251
2387.79 NULL 848 251

-Thanks
R.B

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-12 : 00:25:21

Not sure what you are trying to do but try something like this :


SELECT ISNULL(Convert(varchar,col1),'NULL') + ' ' + ISNULL(Convert(varchar,col2),'NULL')+ ' ' + ISNULL(Convert(varchar,col3),'NULL')+ ' ' + ISNULL(Convert(varchar,col4),'NULL')
FROM YourTable

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 03:23:35

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

ramesh31
Starting Member

11 Posts

Posted - 2007-10-12 : 13:00:57

I'm a writing a sp which sends the result to crystal report,the query I have written here is the sub-report query ( which returns multiple row and multiple columns ) , which i have to combine with my main report query (which return single row) and send the output as single row result to crystal report.

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-12 : 13:03:18

DECALRE @x varchar(8000)
SET @x = ''
SELECT @x = Col FROM Table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-15 : 03:22:16

quote:
Originally posted by X002548

DECALRE @x varchar(8000)
SET @x = ''
SELECT @x = Col FROM Table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Did you mean this?

DECALRE @x varchar(8000)
SET @x = ''
SELECT @x = @x+Col FROM Table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources