Help with this SQl Logic without using UDF

[Vimalsoft Pty Ltd]

Good Day All i have the Following table

DESCR CYCLETEMPLATE ACTV

Al-Dujaili E Dr 2 7688
Al-Dujaili E Dr 4 7688
Al-Dujaili E Dr 6 7688
Allan I Mr 20 8575
Allan I Mr 21 8575
Anderson J Mr 10 7910
Anderson J Mr 11 7910
Anderson J Mr 12 7910

as you can see certain description appears more than once with different CYCLETEMPLATE field but with the same ACTV, what i want to achieve is that i need one record for all the records the match in DESCR AND ACTV and i want the CYCLETEMPLATE to be appended separated by a comma like this

DESCR CYCLETEMPLATE ACTV

Al-Dujaili E Dr 2,4,6 7688

Allan I Mr 20,21 8575

Anderson J Mr 10,11,12 7910

i don't want to use UDF Thank you

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Not Active]

You just need to create a join table. CycleTemplate ID - PK MyTable_ID - PK MyTable Descr

---

I know the language. I've read a book. - _Madmatt

[Mycroft Holmes]

I found this link[^], take a look at the recursive CTE method used. I was sure there was a cute way of doing this but dammed if I can find it now.

Never underestimate the power of human stupidity RAH

[Vimalsoft Pty Ltd]

Thank you guys, The Common Table Expression was slow , i found a Solution and i wrote it this way

SELECT DESCR,ACTV,[CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
FROM #temp
WHERE DESCR = t.DESCR
AND ACTV = t.ACTV
FOR XML PATH('')),1,1,'')
into #TempSummary FROM #temp t
GROUP BY DESCR,ACTV

now i want to sort the CYCLETEMPLATE. I have this UDF

ALTER FUNCTION [dbo].[GetSortedString]
(
@inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN

if dbo.IsStringNumeric( @inputString) = 1
begin
return dbo.GetSortedStringNumeric( @inputString)
end

return @inputString --dbo.GetSortedStringNormal(@inputString)

END

and

ALTER FUNCTION [dbo].[GetSortedStringNumeric]
(
@inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN

declare @outputString varchar(max)
set @outputString = ''
select @outputString = ltrim(rtrim(str(item))) + ',' + @outputString from dbo.fnSplit(@inputString,',') order by item desc
declare @Results int
set @Results = (select dbo.IsMatching(@outputString,'[A-Z ]'))

if SUBSTRING(@outputString,len(@outputString),1) = ',' AND @Results = 0
begin
set @outputString = SUBSTRING(@outputString,1,len(@outputString)-1)
end
else if SUBSTRING(@outputString,len(@outputString),1) = ',' AND @Results > 1
begin
return @outputString
end
return @outputString

END

if i don't sort its Faster but if i sort its a little bit slower. This is how i incoporate it in my query. i just Build a Temp table and sort it there.

SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV

Thanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[nainakarri]

Hi When using function in select query , the query will run very slowly. So you can use views for this which will increase the performance Create a view calling the function (the view should return what ever the function returns). \ Now in your query you can call the view rather than calling the function. This makes performance faster. This is my personal experiance. There may be other ways to do . Do let me know how you achieved it.....

Naina

[Vimalsoft Pty Ltd]

Thanks that is a GOOD Idea. I have resolved it with the Following statement

SELECT DESCR,ACTV,
[CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
FROM #temp
WHERE DESCR = t.DESCR
AND ACTV = t.ACTV Order by CYCLETEMPLATE
FOR XML PATH('')),1,1,'')
into #TempSummary FROM #temp t
GROUP BY DESCR,ACTV

Look at the Bold part it sorts the String. Thanks Guys

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Mycroft Holmes]

Vuyiswa Maseko wrote:

STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))

I TOLD you there was a cute way of doing this, just could not remember it. Please add this to tips and tricks with concatenate in the keywords for next time.....

Never underestimate the power of human stupidity RAH

[Vimalsoft Pty Ltd]

You are right. Thanks :)

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/