For my Report - 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

Transact-SQL (2000)

For my Report

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-01-21 : 22:42:43

Here i am again...

i have this record...

tblTrans
---------------------------------
Description - MRSNo
AA - 1
AA - 3
AA - 4
AB - 2
AB - 3
DD - 2
AC - 1

results should be like this....
Description - MRSNo
---------------------------------
AA - 1,3,4
AB - 2,3
DD - 2
AC - 1

How?

Thanks in advance...

Want Philippines to become 1st World COuntry? Go for World War 3...

Kristen
Test

22859 Posts

Posted - 2005-01-22 : 02:19:46

I suspect with a temporary table, something like:


CREATE TABLE #MyTemp
(
    Description varchar(10),
    MRSNo_List  varchar(255)
)

INSERT INTO #MyTemp
(
    Description
)
SELECT DISTINCT Description FROM tblTrans

UPDATE U
SET  MRSNo_List = COALESCE(MRSNo_List+',', '')
                  + CONVERT(varchar(10), MRSNo)
FROM  #MyTemp U
      JOIN tblTrans T
          ON T.Description = U.Description
WHERE MRSNo IS NOT NULL

SELECT *
FROM   #MyTemp
ORDER BY Description

Kristen

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-01-22 : 02:45:17

but i got a wrong result...

Description - MRSNo
---------------------------------
AA 3
AC 2
BB 3
CC 1

Want Philippines to become 1st World COuntry? Go for World War 3...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-22 : 08:20:00

--When you want to turn SqlServer into a report writer you end up with some nasty queries...

set nocount on
declare @tb table ([description] varchar(2), MRSNo int)
insert @tb values ('AA', 1)
insert @tb values ('AA', 2)
insert @tb values ('AA', 3)
insert @tb values ('AA', 4)
insert @tb values ('AB', 2)
insert @tb values ('AB', 3)
insert @tb values ('DD', 2)
insert @tb values ('AC', 1)

-- if your list of MRSNo values are a determinate list this is an efficient, loopless solution:
Select rol.[description]
,MRSNos = isNull(mrs1, '')
+ case when mrs1 is not null then coalesce(', ' + mrs2, '') else isNull(mrs2,'') end
+ case when coalesce(mrs1,mrs2) is not null then coalesce(', ' + mrs3, '') else isNull(mrs3,'') end
+ case when coalesce(mrs1,mrs2,mrs3) is not null then coalesce(', ' + mrs4, '') else isNull(mrs4,'') end
From (
Select det.[Description]
,max(mrs1) mrs1
,max(mrs2) mrs2
,max(mrs3) mrs3
,max(mrs4) mrs4
From (
Select Description
,mrs1 = case when mrsno = 1 then convert(varchar(2), mrsno) else null end
,mrs2 = case when mrsno = 2 then convert(varchar(2), mrsno) else null end
,mrs3 = case when mrsno = 3 then convert(varchar(2), mrsno) else null end
,mrs4 = case when mrsno = 4 then convert(varchar(2), mrsno) else null end
From @tb
) as det
Group by det.[description]
) as rol

-- if your list of MRSNo values are NOT a determinate list, you would need something like this:
declare @desc varchar(2)
,@mrsNos varchar(50)
declare @summary table ([Description] varchar(2), mrsNos varchar(50))
insert @summary ([Description])
Select [Description]
From @tb
Group by [Description]

declare crs cursor for
Select [description] from @summary
open crs
fetch next from crs into @desc
while @@Fetch_status = 0
Begin
set @mrsNos = null
Select @mrsNos = coalesce(@mrsNos + ', ' + convert(varchar(2), mrsno), convert(varchar(2), mrsno)) from @tb where [description] = @desc
update @summary set mrsnos = @mrsNos where [description] = @desc
fetch next from crs into @desc
End
close crs
deallocate crs

Select * from @summary

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-22 : 08:23:25

woops, my tabbed indenting disapeared when I pasted the above solutions...sorry, I'll use spaces next time.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-22 : 09:30:34

quote:
Originally posted by TG

woops, my tabbed indenting disapeared when I pasted the above solutions...sorry, I'll use spaces next time.

put the formatted code inside code tags:
[ code]...formatted code...[/ code]
(without the spaces)

see forum faq.

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-22 : 09:43:36

Thanks, rockmoose! I guess I should have looked there before I started polluting the waters :)
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-22 : 10:07:10

you said this is for a report -- it is often much easier and more efficient to do this on the report itself. How are you producing the output for this report?
- Jeff

Kristen
Test

22859 Posts

Posted - 2005-01-22 : 10:16:21

"but i got a wrong result..."

You're right. Only the last row update is succeeding, the preceeding rows are not being used. Needs a more complicated UPDATE. Or the LIST function from Sybase :-(

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-22 : 11:50:01

quote:

Needs a more complicated UPDATE. Or the LIST function from Sybase :-(

... or something like this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647 (second comment down, by Byrmol)

a UDF is the most efficient way to handle this, if you don't do it at the presentation layer. Keep it simple.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources