Comma delimited string problem or some advice to create a fake arra. 1

[rogerzebra]

Hi All,

I'm trying to create a comma delimited list of classcodes and I can't see why the piece of my code beneath doesn't working. The purpose is to add this snippet to a main piece of code and to publish a report on the Reporting Services. I first tried to use a cursor that obviously doesn't work in reporting services? I don't know perhaps that would work but it was way to slow. What I really need is a comma delimit list without a function or if someone could give some good advice on how to make this work with a fake array? So, obviously this is something that I need some advice on, I really would appreciate if someone could help me out here, thanks in advance.

declare @ClassCodeList varchar(1000)

	CREATE TABLE #ClassCodeTempList
	(
		SystemID int 
		
	)

	DECLARE @SystemID varchar(10), @Pos int

	SET @ClassCodeList = LTRIM(RTRIM(@ClassCodeList))+ ','
	SET @Pos = CHARINDEX(',', @ClassCodeList, 1)

	IF REPLACE(@ClassCodeList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @SystemID = LTRIM(RTRIM(LEFT(@ClassCodeList, @Pos - 1)))
			IF @SystemID <> ''
			BEGIN
				INSERT INTO #ClassCodeTempList(SystemID) VALUES (CAST(@SystemID AS int))
			END
			SET @ClassCodeList = RIGHT(@ClassCodeList, LEN(@ClassCodeList) - @Pos)
			SET @Pos = CHARINDEX(',',@ClassCodeList, 1)

		END
	END	

SELECT c.SystemID, c.ClassCode
	from ClassTbl c
		JOIN 
		#ClassCodeTempList t
		ON c.SystemID = t.SystemID

drop table #ClassCodeTempList

 

[bborissov]

Drop using Temp table, use TABLE variable instead. It is faster and no need to DROP it after all finished.

Also I don't see where you set a value to @ClassCodeList

That:
declare @ClassCodeList varchar(1000)
...
SET @ClassCodeList = LTRIM(RTRIM(@ClassCodeList))+ ','

Will give you NULL as a result and you are not going into WHILE loop.
If you receive @ClassCodeList as a parameter to SP then that is another option

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[rogerzebra]

Hi bborissov,
I really appreciate your respond. I found another thread answered by you that is a much more brilliant solution with less code than I have presented. I agree with you when it comes to using declared tables instead of tmp tables, sometimes though you need to use tmp tables that executes much faster, especially if you want the result to be deployed as a report on the reporting services.

Still have a question though. How do I join to the declared value @cols (systemid)in this snippet?

DECLARE @cols varchar(8000)

SELECT @cols = ISNULL(@cols+',', '')+SystemID
       FROM tblCoverage
SELECT @cols

I need to retrieve the classcode for each systemid and join a policy table.

 

[bborissov]

Could you post some simple data and what you want as a result?
Didi you want just a recordset with ONE record, that contains both, SystemID and ClassCode as a two huge varchar fields or you want a pure recordset with only two columns?
If the first:

DECLARE @sysIDs  varchar(8000)
DECLARE @classes varchar(8000)

SELECT @sysIDs  = ISNULL(@sysIDs+',', '')+SystemID,
       @Classes = ISNULL(@Classes+',', '')+ClassCode
       FROM tblCoverage

SELECT @sysIDs, @Classes

But I could answer you tomorrow, because I'm too tired right now and I'm going to sleep.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[rogerzebra]

Thanks again man, yes that would be great if you could help me tomorrow. I need to come up with a solution on this before monday. The problem is that I can't join to a string of systemId's.

I need to do something like this

DECLARE @cols varchar(8000)
declare @t1
(systemid varchar(50)
,classcode varchar(8000)
)
insert into @t1

SELECT c.systemid,c.classcode
FROM tblcoverage c
where @cols = ISNULL(@cols+',', '')+ cast(c.classcode as varchar)

select p.policynumber
,a.classcode
from @t1 a 
inner join tblpolicy p on a.systemid = p.systemid

I expect a result like this

systemId 1
policynumber 1001
classcode 10
classcode 20
classcode 30

systemId 2
policynumber 1002
classcode 10
classcode 20
etc..

instead I get this

systemid 1
policynumber 1001
classcode 10
systemid 1
policynumber 1001
classcode 20



Thanks again its greately appreciated

 

[bborissov]

rogerzebra,
Do the representation of the data in your frontend (or report).
Order the resultset by systemId, PolicyNumber and classcode and then in report make a group by SystemId
In the Group header print SystemId
Make a new group by Policy number,
in the Group header print PolicyNumber.
In detail band print ClassCode.
That way you will get what you want w/o and hard to write and read SQL Selects.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[rogerzebra]

hi bborissov, thanks once again for your respond, I do need to solve this issue in the backend though. This part is just a small part of the whole query and it contains more than just one section of comma delimited lists. This starts to qive me some headaches.

I found a piece of code that does a better job than my initial one which does 90% of the job. But I still need to solve 2 issues though.

First the resultset cointains a lot of commas in between that needs to be filtered out. I have tried using coalesce without success.
Second I'm trying to insert the "PRINT" resultset into a temp table and that turned out to be tricky as well. Please help me out here.

Declare @StartDt	datetime
		,@EndDt		datetime
set		@StartDt	='10.01.2008'
set		@EndDt		='10.31.2008' 

declare @t1 table
(Systemid varchar(8000)
,Classcode varchar(50)
)
declare @p varchar(8000)
declare @i char(5)
declare @sm int
declare @m int

--print 'SystemID' + 'Classcode'
--print '----- ' + '------------------------------------------'
set @p = ''
select top 1 @m = c.systemid 
from tblcoverage c
inner join tblpolicy p on c.systemid = b.systemid
where effectivedt between @StartDt and @EndDt
order by c.systemid
set @sm = 0 
while @m <> @sm
begin
--------------
set @sm = @m
select @i = c.systemid, @p = case	when @p = '' 
									then c.classcode
									else COALESCE(@p + ',','')+ c.classcode 	
									end 
from tblcoverage c
inner join tblpolicy p on c.systemid = b.systemid
where c.systemid = @m
and c.cmmcontainer ='policy'
--print id_no, and comma delimited string
print @i + ' ' + @p 

select top 1 @m = c.systemID
from tblcoverage c
inner join tblpolicy p on c.systemid = b.systemid
where c.systemID > @sm	
order by c.systemID
set @p = ''
end

/*******This Part is not working. I'm trying to insert the print result into a tmp table instead of the set @p ='' in the end. But I get the error that I need to declare the It should be something like this
[b]set @p = 'INSERT INTO '+@t1+' VALUES('''+CONVERT(VARCHAR(8000),SystemID)+''','''+convert(varchar(50),Classcode)' [/b]

**************/

 

[rogerzebra]

********Solved*********
Hi bborissov,
I did it your way and finally got it right. Thanks so much.