| Author |
Topic |
|
satish.gorijala
Posting Yak Master
182 Posts |
 Posted - 2009-05-20 : 05:22:22
|
| From below table, i need to retrive all data based on Col4. (i.e group by Col4.) Col1 & Co2 be same through out the table. Col3 must be append by comma based on Col4. See the output below by getting an idea.Original TableCol1 Col2 Col3 Col4SAME HISP Arge AlcibarSAME HISP Colo AlcIbarSAME HISP Colo AlcIbarSAME HISP Colo AlmerIaSAME HISP Span AlmerIaSAME HISP Arge AmarillaSAME HISP Peru AnivoleSAME HISP Arge AnteroSAME HISP Colo AnteroThe Ouput be like thisCol1 Col2 Col3 Col4SAME HISP Arge,Colo AlcibarSAME HISP Colo,Span AlmerIaSAME HISP Arge AmarillaSAME HISP Peru AnivoleSAME HISP Arge,Colo AnteroG. Satish |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-05-20 : 05:37:27
|
| use stuff , it will be more useful to u |
 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-05-20 : 05:37:52
|
| select col1,col2,stuff((select ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-05-20 : 05:52:02
|
Thank you. Its really usefulquote:
Originally posted by bklr
select col1,col2,stuff((select ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t
G. Satish |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-05-20 : 05:52:52
|
quote:
Originally posted by satish.gorijala
Thank you. Its really usefulquote:
Originally posted by bklr
select col1,col2,stuff((select ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t
G. Satish
welcome |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-05-20 : 06:00:22
|
When i use this query..i am almost very near to my requirement. but the repeating occurs. See Column3 in below output. The "Colo" is repeating. How to avoid this. I just want "Arge,Colo"select distinct Column1,Column2,stuff((select ','+Column3 from tablename where Column4 = t.Column4 for xml path('')),1,1,'') as Column3 , Column4from tablename t Order by Column4the output i got isSAME HISP Arge,Colo,Colo AlcibarSAME HISP Colo,Span AlmerIaquote:
Originally posted by bklr
select col1,col2,stuff((select ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t
G. Satish |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-05-20 : 06:01:26
|
ok, Finally i got it. I use distinct in internal query.select distinct Column1,Column2,stuff((select distinct ','+Column3 from tablename where Column4 = t.Column4 for xml path('')),1,1,'') as Column3 , Column4from tablename t Order by Column4quote:
Originally posted by bklr
quote:
Originally posted by satish.gorijala
Thank you. Its really usefulquote:
Originally posted by bklr
select col1,col2,stuff((select ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t
G. Satish
welcome
G. Satish |
|
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-05-20 : 06:01:35
|
quote:
Originally posted by satish.gorijala
When i use this query..i am almost very near to my requirement. but the repeating occurs. See Column3 in below output. The "Colo" is repeating. How to avoid this. I just want "Arge,Colo"select distinct Column1,Column2,stuff((select ','+Column3 from tablename where Column4 = t.Column4 for xml path('')),1,1,'') as Column3 , Column4from tablename t Order by Column4the output i got isSAME HISP Arge,Colo,Colo AlcibarSAME HISP Colo,Span AlmerIaquote:
Originally posted by bklr
select col1,col2,stuff((select ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t
G. Satish
select col1,col2,stuff((select distinct ','+col3 from tablename where col4 = t.col4 for xml path('')),1,1,'') as col3 , col4from tablename t |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
Need Query