Data presentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data presentation

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-01-24 : 21:21:12

hi

is it possible to present (concatnat) the row data in a coloum..
ie.

i have query return this data

Owner ID DEVICE ID Talkgroup
881374 000100249453050 61
881374 000100249453050 62
881405 000101778765080 1
881405 000101778765080 2

it should return like this

Owner ID DEVICE ID Talkgroup
881374 000100249453050 61,62
881405 000101778765080 1,2
several rows like this to go

thanks

======================================
Ask to your self before u ask someone

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-25 : 03:56:31

Don't think it's possible in a single query - well maybe with a function.

select OwnerID, DEVICEID, Talkgroup = convert(varchar(1000),min(Talkgroup)), LastTalkgroup = min(Talkgroup)
into #a
from tbl
group by OwnerID, DEVICEID

while @@rowcount > 0
update #a
set Talkgroup = Talkgroup + ',' + convert(varchar(1000),min(Talkgroup)), LastTalkgroup = min(Talkgroup)
from tbl
where #a.OwnerID = tbl.OwnerID
and #a.DEVICEID = tbl.DEVICEID
and #a.Talkgroup < tbl.Talkgroup
and exists
(select *
from tbl
where #a.OwnerID = tbl.OwnerID
and #a.DEVICEID = tbl.DEVICEID
and #a.Talkgroup < tbl.Talkgroup
)

select OwnerID, DEVICEID, Talkgroup
from #a

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-01-29 : 00:58:54

thanks

i have a stored procedure to do this but it take hell lot of time
and i am using curors... how well u function perform does it be faster than cursors....

thanks

======================================
Ask to your self before u ask someone

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-01-29 : 16:14:58

This seems to work. HTH Louis.

----------------------------------------------------------------
create table #me
(ownerID int, deviceID varchar(100), talkgroup int, notes varchar(100), counter int)
insert into #me
select ownerID=881374, deviceID=000100249453050, talkgroup=61, notes=null, counter=null
union
select ownerID=881374, deviceID=000100249453050, talkgroup=62, notes=null, counter=null
union
select ownerID=881405, deviceID=000101778765080, talkgroup=1, notes=null, counter=null
union
select ownerID=881405, deviceID=000101778765080, talkgroup=2, notes=null, counter=null
union
select ownerID=123456, deviceID=000111111111111, talkgroup=3, notes=null, counter=null
union
select ownerID=123456, deviceID=000111111111111, talkgroup=4, notes=null, counter=null

declare @text varchar(1000)
declare @ownerID int
declare @deviceID varchar(100)
declare @counter int

while exists( select * from #me where notes is null )
begin
select @ownerID=ownerID, @deviceID=deviceID from #me where notes is null
select @text = null,@counter=0
update #me
set @text = notes = isnull(cast(talkgroup as varchar),'') +','+ isnull(@text,'')
,@counter = counter = @counter + 1
where ownerID=@ownerID and deviceID=@deviceID
end

select a.ownerID, a.deviceId, notes=left(notes,len(notes)-1)
from #me as a
join
(select ownerid,deviceid,counter = max(counter) from #me group by ownerID, deviceID) as b
on a.ownerid=b.ownerid and a.deviceid=b.deviceid and a.counter=b.counter

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 02:59:08

thanks....

i have a procedure which does the same.... almost
but i am using cursors....
is there a diffrence between cursor and
while exists( select * from #me where notes is null )
begin
...
...
end

======================================
Ask to your self before u ask someone

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-05 : 03:18:57

The difference is that the cursor will process only one row per loop. Lou's solution can process many rows per loop, and with larger rowsets will be much faster than a cursor solution. You will not see much difference if you're processing < 5,000 rows, but any more than 10,000 should show a marked improvement.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 04:53:02

can we replace the cursors when we do a batch process...

while exists( select * from #me where notes is null )
begin
....
....
end

does mean .. loop for all the rows where notes is null

thanks

======================================
Ask to your self before u ask someone

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-05 : 17:31:30

quote:
can we replace the cursors when we do a batch process...does mean .. loop for all the rows where notes is null

Yes, you can do that, but we'll need your code before we can suggest a solution.

The only thing a cursor does, THE ONLY THING, is move from one row to the next. It's a row positioning device. Period. It does not UPDATE or DELETE anything; you still must use those SQL commands to perform those actions, they are simply limited to WHERE CURRENT OF CURSOR.

If you take the SELECT statement with which you define the cursor you're using, utilize the same JOIN and WHERE clauses, and include them in an UPDATE statement, you'll do the same thing the cursor procedure does, but you'll affect the ENTIRE set, with one statement in one operation, instead of several statements over thousands or more rows.

Do yourself a favor: look at all of your code, and wherever you see a CURSOR declaration in that code, put it on your shit list, and vow that you will fix it to use set-based operations instead. You will not believe the improvement in performance that you will see.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 20:39:33

thanks...

i dont want to use cursors so i did not write the code....
i was looking for alternative......

the purpose of this code is to recommend the best sutiable rateplan based on the rate plan....

sorry i cant give the code right now i dont have...

using a query i get this result...

AccountID | FULL_USAGE_QTY | plan1 | raterank | Free_Min | Rate
51874 346 Saver 0 260 0.17
51874 250 Saver 1 260 0.08
51874 43 Saver 2 260 0.05
31812 110 PlanX 0 0 0.21
31812 50 PlanX 1 0 0.13
31812 75 PlanX 2 0 0.07

for every account i need to take out the free min
in order 1,0,2
and the remaining min should be charged ...

a big base so i dont want to use cursors.....
and i have to repeat it for the remaining rate plans

hope some one will come up with a solution....

thanks....

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources