Fairly difficult question (at least for me)

[DanC]

I'm trying to figure out the best way to get the results I want:

mytable

tabid distid colid cnt
1 Dist 1 0 189
1 Dist 1 1 433
1 Dist 1 2 8
1 Dist 2 0 55
1 Dist 2 1 2
1 Dist 2 2 444


I need it to display like this

distid col1 col2 col3
Dist 1 189 433 8
Dist 2 55 2 444


The problem comes in that I need to be able to re-use this for different clients who will have different numbers of columns (any where up to 255, colid can be 0-254). I only want to display valid columns for each client dataset.

I'm kind of lost on how to accomplish this, so any help would be greatly appreciated.

 

[bperry]

Are the clients represented by the 'DistId' column, or is that something else?


I notice that the vertical display starts numbering at 0, but the horizontal presentation starts at 1 (i.e. ColId + 1). That's deliberate, right?

 

[Saama5]

I am assuming that your table name is tab1
I know this way to do it.

create procedure abc
as
declare @distid nvarchar(50)
declare @colid int
declare @cnt int
create table #temptbl1
(distid nvarchar(50),
col1 int,
col2 int,
col3 int)
declare x1 cursor for select distinct distid from tab1
open x1
fetch next from x1 into @distid



while @@fetch_status = 0
begin
insert into #temptbl1 (distid) values (@distid)

declare x2 cursor for select colid,cnt from tab1 where distid = @distid
open x2
fetch next from x2 into @colid,@cnt

while @@fetch_status = 0
begin

if @colid = 0
update #temptbl1 set col1 = @cnt where distid = @distid
if @colid = 1
update #temptbl1 set col2 = @cnt where distid = @distid
if @colid = 2
update #temptbl1 set col3 = @cnt where distid = @distid
fetch next from x2 into @colid, @cnt


end
close x2
deallocate x2
fetch next from x1 into @distid
end
close x1
deallocate x1
select * from #temptbl1
drop table #temptbl1

 

[DanC]

thanks for the procedure. It works, but the problem is that I don't have a set number of columns for table. The number of columns varies based on the client. I was trying to figure out a way to have a procedure like yours, but instead of a static number of columns, it would dynamically create the output based on the number of distinct values in the colid column.

bperry, the distid column actually just represents an addition grouping band along with TabId. The identity of the client is not necessary as they will all have thier own databases. And yes the number is like that intentionally.

thanks again for the help

 

[bperry]

danc:
I have a stored procedure that does what you want. (There were at least 3 similar posts in the last few days.) Automatically determines the number of columns required.
But uses 'dynamic sql' (EXEC @SQLSTRing) to run, which not everybody likes.

If the cursor approach above doesn't work out for you, I could post my solution if you case to see it.

bp

 

[DanC]

that would be great if you could post it, as the cursor approach doesn't seem to be working for me

 

[bperry]

Okay, later tonight or tomorrow.
May as let me have your table name (and column names if different than shown) so I can set up with the right names when I am testing.

bp

 

[tlbroadbent]

There are several sample procedures available online. See Thread183-276251 for some links. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DanC]

thanks guys with a little tweaking and modification I was able to apply it directly to my issue.