how can i write query for this

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

how can i write query for this

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2009-01-06 : 05:29:35

in my table i have coilid and splitid
each coil may have more than one splitid
if it have morethan one splitid, it have to display with came saparation

i worte a query for this but it is tabking more than 40 min
in my query i kept while loop and geeting the out put
but its exicution time is very late
could u plese give any other alter native

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 05:31:34

use for xml path()
see in books online for it

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 05:33:42

try this
select stuff((select ',' + cast(splitid as varchar(12)) from urtable where coilid = c.coilid for xml path('')),1,1,'') from urtable c

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 05:34:51

May Be u get the results like this......

select DISTINCT coilid,stuff((select ',' + splitid from tset16 where coilid = t.coilid for xml path('')),1,1,'') from test16 t

Or Post some sample data....wid the expected results...
Thanks....

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 05:38:11

slight modification to bklr solution just use distinct to avoid duplicate rows

select distinct coilid,stuff((select ',' + cast(splitid as varchar(12)) from urtable where coilid = c.coilid for xml path('')),1,1,'') from urtable c

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 05:52:53

quote:
Originally posted by ashishashish

May Be u get the results like this......

select DISTINCT coilid,stuff((select ',' + splitid from tset16 where coilid = t.coilid for xml path('')),1,1,'') from test16 t

Or Post some sample data....wid the expected results...
Thanks....

I think ur Query doesn't work well because splitid integer type,
so u have to convert splitid into varchar datatype.
Mr.Raky suggested the correct syntax for query.

boreddy
Posting Yak Master

172 Posts

Posted - 2009-01-06 : 05:57:01

by useing these queries i am getting repeted splitid

for example
coilid 20002 it has two splitids like 123 and 234
i need to display like this
colilid splitids
20002 123,234

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 06:05:48

declare @temp table (coilid int, splitid int)
insert into @temp select 20002 ,123 union all
select 20002 , 124 union all
select 20002,212 union all
select 2112,323 union all
select 2112, 423

select distinct coilid,stuff((select ','+ cast(splitid as varchar(12)) from @temp where coilid = e.coilid for xml path('')),1,1,'') from @temp e

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 06:08:27

quote:
Originally posted by bklr

declare @temp table (coilid int, splitid int)
insert into @temp select 20002 ,123 union all
select 20002 , 124 union all
select 20002,212 union all
select 2112,323 union all
select 2112, 423

select distinct coilid,stuff((select ','+ cast(splitid as varchar(12)) from @temp where coilid = e.coilid for xml path('')),1,1,'') from @temp e

The Above Query was correct , but small modification in that Query
put distinct in the select list in Stuff

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 06:25:30

Yes I take it as varchar Sry for that,,
If ur data Type is Int Then Use ,,,,
cast(splitid as varchar(50))

Thanks...

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 06:34:17

create table test17(coilid int, splitid int)
insert into test17
select 1245 ,123 union all
select 4578 ,124 union all
select 1245 ,212 union all
select 1245 ,323 union all
select 4578 ,423 union all
select 2485 ,623 union all
select 4578 ,213 union all
select 2485 ,318

select distinct coilid,stuff((select ','+ cast(splitid as varchar(12)) from test17 where coilid = t.coilid for xml path('')),1,1,'') from test17 t

And Result rrr

coilid No coloumn name

1245 123,212,323
2485 623,318
4578 124,423,213

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 06:49:38

quote:
Originally posted by ashishashish

create table test17(coilid int, splitid int)
insert into test17
select 1245 ,123 union all
select 4578 ,124 union all
select 1245 ,212 union all
select 1245 ,323 union all
select 4578 ,423 union all
select 2485 ,623 union all
select 4578 ,213 union all
select 2485 ,318

select distinct coilid,stuff((select distinct ','+ cast(splitid as varchar(12)) from test17 where coilid = t.coilid for xml path('')),1,1,'') from test17 t

And Result rrr

coilid No coloumn name

1245 123,212,323
2485 623,318
4578 124,423,213

Put distinct in the select list.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 07:05:51

Ohkkkkkkkk Got It Sirrrrr,
Thanks.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources