December 2, 2010 at 10:46 am
Say, I have the original table like
ID TERM CLASS
---------------
304 20103 EE2
156 20102 CS3
167 20101 MC1
167 20101 SE3
167 20104 SE4
167 20105 SE4
Now, What might be the solution if I want the resulting table to concatenate the 'CLASS' fields only 'if a single TERM entry has associated two different CLASS entries'
and leave everything else intact as shown below.
ID TERM CLASS
---------------
304 20103 EE2
156 20102 CS3
167 20101 MC1,SE3
167 20104 SE4
167 20105 SE4
December 2, 2010 at 11:51 am
You might want to search this site for "string concatenate for xml path".
I'm sure FOR XML PATH will do the trick.
December 8, 2010 at 6:35 am
declare @t table ( ID int , TERM varchar(20), CLASS nvarchar(20))
insert into @t
select 304 ,20103, 'EE2'
union select 156 ,20104, 'CS3'
union select 167,20144,'MC1'
union select 167 ,20144,'SE3'
select distinct final.id,final.TERM,substring(final.results,2,len(final.results))
from
(
select t.id,t.TERM,(select ',' + class from @t m where m.id = t.id for xml path ('')) results from @t t
) final
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 8, 2010 at 2:35 pm
Thank you Bhuvanesh.
December 8, 2010 at 3:29 pm
Did you realize that your requirements did change between the original and your latest post?
TERM was unique per ID and there were no duplicate CLASS values per ID either nor did you mention it in any way. Please keep in mind that we don't see what you see. All we have is the sample data and the description you provide.
So, it's not an issue of Bhuvanesh providing the wrong solution. You've provided the wrong/incomplete requirements.
Using Bhuvanesh's sample data (See how you should provide sample data for any future questions?), here's the modified version. Please note the additional effort to sort the rows exactly as you specified...
@Bhuvanesh: Thank you for providing ready to use sample data! Your previous solution did cover all original requirements.
SELECT
final.id,
MAX(final.TERM) AS Term,
MIN(SUBSTRING(final.results,2,LEN(final.results))) AS CLASS
FROM
(
SELECT
t.id,
t.TERM,
(SELECT ',' + CLASS
FROM @t m
WHERE m.id = t.id
GROUP BY CLASS
FOR XML PATH ('')
) results
FROM @t t
) final
GROUP BY final.id
ORDER BY LEFT( final.id,1) DESC, final.id
December 8, 2010 at 3:53 pm
ERROR!!
December 8, 2010 at 4:04 pm
Hmmm....
Given the solutions already provided I'd recommend you study how both versions work.
I'm sure you'll find the solution by yourself.
Strong hint: Have a look at the WHERE clause as well as the GROUP BY clause used in the query.
Don't get me wrong. I'm not refusing to help you. But I refuse to replace you... 😉
December 8, 2010 at 7:12 pm
select distinct final.ID,final.TERM,substring(final.results,2,len(final.results))
from
(
select t.ID,t.TERM,(select ',' + class from Demo m where m.term = t.term and m.id=t.id
for xml path (''))
results from Demo t
) final
--------------------------
Finally, I came up with above solution which is working perfectly. Thanks for your help guys.:-)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply