stored procedure

Question

stored procedure

Mike Levan

SSChampion

Points: 14201
More actions
August 31, 2005 at 6:44 am

#87464

I have tables
Skill(skillID,skillName)
Topic(TopicID,code,Descr).
I am using the following select statement
-------------------------------------------------------------------------------------
select skillname,code,descr from skill s inner join topic t on s.skillid=t.skillid
here is the result
skillname code descr
Computers --------A001A--Access Basic
Computers --------A001B--Access Intermediate
Communications -----B008---Business Writing
Communications -----B009---Communications Skills
Human Development----C003---Coping With Change
Human Development----C004---Customer Service
Human Development----C008---Time Management
---------------------------------------------------------------------------
I want to display this result in my App but I dont want to repeat the skillname as in the result. It shud be like only 1 skillname, under that code & descr for tht skillname. There is a chance for skillname field to grow.
I understand this is a presentation stuff but my App Developers want me to do it through store proc.
How can i perform this automatically from any store proc, if so how?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 1

Insert the results into a temp table with an added IDENTITY (INT, 1, 1), in the order you want them displayed. Then use an UPDATE to do the magic, something like:

UPDATE #tempresults
SET #tempresults.skillname = ''
WHERE EXISTS (SELECT 1
                FROM #tempresults t2
                WHERE #tempresults.skillname = t2.skillname
                  AND t2.identitycol < #tempresults.identitycol)

Then select the results from your temp table, ordering by your identity column.

And, by the way, I think your app guys are being unreasonable.

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Lee - applause from the stands for that solution...

I was just going to ask jp to kick the app developers..

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I still vote for the kick... simple reporting task best handled AT THE CLIENT.

Very nice solution anyhow if you want to waste cpu time on the server.

Mike Levan SSChampion Points: 14201 More actions · Answer 4

Mike Levan

SSChampion

Points: 14201

August 31, 2005 at 2:35 pm

#586656

Thanks guys

Anyways I was able to get that result.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 5

declare @Skill table(skillID int,skillName varchar(30))

declare @Topic table(skillid int,code varchar(30),Descr varchar(30))

insert into @skill

select 1,'Computers' union all

select 2,'Communications' union all

select 3,'Human Development'

insert into @topic

select 1,'A001A','Access Basic' union all

select 1,'A001B','Access Intermediate' union all

select 2,'B008','Business Writing' union all

select 2,'B009','Communications Skills' union all

select 3,'C003','Coping With Change' union all

select 3,'C004','Customer Service' union all

select 3,'C008','Time Management'

select skillname,code,descr from @skill s inner join @topic t on s.skillid=t.skillid

select case when d.code is null then '' else skillname end,t.code,descr

from @skill s inner join

(select skillid , min(code) as code from @topic group by skillid) d

on s.skillid=d.skillid

right outer join

@topic t on t.skillid=d.skillid and t.code=d.code

Kindest Regards,

Vasc

dbwhz Old Hand Points: 348 More actions · Answer 6

"Insert the results into a temp table with an added IDENTITY (INT, 1, 1)"

How would you do that? :/

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 7

> How would you do that?

Given the original query:

select skillname,code,descr from skill s inner join topic t on s.skillid=t.skillid

Something along these lines:

SELECT IDENTITY (INT, 1, 1) identitycol
     , skillname
     , code
     , descr
INTO #tempresults
FROM   skill s
JOIN topic t ON s.skillid = t.skillid
ORDER BY skillname
       , code
UPDATE #tempresults
SET #tempresults.skillname = ''
WHERE EXISTS (SELECT 1
                FROM #tempresults t2
                WHERE #tempresults.skillname = t2.skillname
                  AND t2.identitycol < #tempresults.identitycol)
SELECT skillname
     , code
     , descr
FROM #tempresults
ORDER BY identitycol