Use CoalEsce - 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

Use CoalEsce

Author

Topic

vijaynetpart
Starting Member

18 Posts

Posted - 2009-05-04 : 00:46:31

Code PurId
001 1
002 1
003 1
004 2
005 2

I need below result

Code PurId
001,002,003 1
004,005 2

Help me?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-04 : 01:05:04

May Be Like this...

declare @T table (code int ,purid int)
insert into @T
select 001, 1 union all
select 002, 1 union all
select 003, 1 union all
select 004, 2 union all
select 005, 2

select DISTINCT purid,stuff((select ',' + '00'+ cast(code as varchar(10)) from @T where purid = t.purid for xml path('')),1,1,'') as code from @T t

Thanks...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

vijaynetpart
Starting Member

18 Posts

Posted - 2009-05-04 : 01:08:10

Dear ashishashish,
Thanks your reply...Is this result is possible using COALESCE?

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-05-04 : 01:32:34

Hi,
I dont think so,
The purpose of COALESCE functon is to return first non null value among the value list.

vijaynetpart
Starting Member

18 Posts

Posted - 2009-05-04 : 01:34:13

Dear ashishashish,
But in this query used a (+ '00'+)....This is not a fixed one...It may be change in future.

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-04 : 01:35:14

Hi Vijay,
This is not possible using coalesce. Which version of SQL server you are using.

malay

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-04 : 01:35:36

Do you know how a COALESCE Works...
well i think any solution which satisfies your requirement works fine for you..
isn't it??

quote:
Originally posted by vijaynetpart

Dear ashishashish,
Thanks your reply...Is this result is possible using COALESCE?

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-04 : 01:36:19

Can you mention datatype of Code column

malay

vijaynetpart
Starting Member

18 Posts

Posted - 2009-05-04 : 01:49:09

i m using sqlserver2000

code nVarchar(50)
PurId Int

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-04 : 01:59:53

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

Failing to plan is Planning to fail

vijaynetpart
Starting Member

18 Posts

Posted - 2009-05-04 : 07:06:54

ok ashishashish,
Your query was good..actually i m having a more than 500 datas....How can extend the size...in ur query

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-04 : 07:47:35

what u mean by --Having more than 5000 datas --
well i think.. you want it like this just try it once....

declare @num Table(a int,b int)
insert into @num
select number,1 from master..spt_values where type='p' and number<=500
select DISTINCT b,stuff((select ',' + cast(a as varchar(10)) from @num where b = t.b for xml path('')),1,1,'') as AA from @num t

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-04 : 07:56:04

you can also take a look at the link wich is suggested by Madhivanan sir...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

vijaynetpart
Starting Member

18 Posts

Posted - 2009-05-05 : 00:50:14

ok ashishashish,
Its return only 0 to 88.
But table datas are 0 to 500....

I need to get the all datas in the table 0,1,2,3,..to ,499,500

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-05 : 01:06:13

quote:
Originally posted by malaytech2008

This is not possible using coalesce.

Yes it is.
But it requires a lot of JOINs.

E 12°55'05.63"
N 56°04'39.26"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-05 : 01:11:53

quote:
Originally posted by vijaynetpart

ok ashishashish,
Its return only 0 to 88.
But table datas are 0 to 500....

I need to get the all datas in the table 0,1,2,3,..to ,499,500

In my case it returns all the records....
i tested it and after that i posted it here.....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources