Consolidating Multiple Rows into one

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)

Consolidating Multiple Rows into one

Author

Topic

jmcbride
Starting Member

24 Posts

Posted - 2004-05-07 : 19:41:50

I have the following subset of data (the table is much larger with many more individuals and tite/departments:

Name ID Title Department
ROMO 123 RESEARCHER BIC
ROMO 123 PROFESSOR SOC
ROMO 123 RESEARCHER SOC

I would like for the above to display in one row like follows:
Name ID Title Department
ROMO 123 RESEARCHER BIC
PROFESSOR SOC

Can SQL do something like this? This is one table, and an individual can have multiple titles and departments, but there name and ID will always be the same.

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 20:15:23

You can concatenate the rows into a csv string.

Create a function
create function csvtbl
(
@name varcar(20)
@id int
)
returns varchar(1000)
AS
begin
declare @csv varchar(1000)
select @csv = coalesce(@csv+',', '') + Title + ' ' + Department
from tbl
where name = @name
and id = @id
return @csv
end
go

then call it by

select name, id, dbo.csvtbl(name, id)
from tbl
group by name, id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jmcbride
Starting Member

24 Posts

Posted - 2004-05-28 : 16:59:32

hmm, I couldn't get this to work properly. It seems that upon creating the function (modified the code a little):


create function csvMaker(
@name varchar(20),
@id int
)
returns varchar(1000)
AS
begin
declare @csv varchar(1000)
select @csv = coalesce(@csv+',', '') + Title + ' ' + Department
from tbl
where names = @name
and ids = @id
return @csv
end
go

And running...


select csvMaker(last_name, eid)
from webstf2
group by last_name, eid

I get the following error:

Server: Msg 195, Level 15, State 10, Line 17
'csvMaker' is not a recognized function name.

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 17:02:37

SELECT dbo.csvMaker...

You must specify the owner when calling functions.

Tara

jmcbride
Starting Member

24 Posts

Posted - 2004-05-28 : 17:05:35

How do you specify the owner if it is something like this:
'domain\user'

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 17:18:44

I've never had to call a function that way, always have used dbo.

Try user.csvMaker.

BTW, using dbo is the recommendation for objects.

Tara

jmcbride
Starting Member

24 Posts

Posted - 2004-05-28 : 17:21:29

Yeah, I've tried 'user.csvMaker' and I keep getting "Server: Msg 208, Level 16, State 1, Line 17
Invalid object name 'jmcbride.csvMaker'."

Is there a way to change the owner? I've looked everywhere in properties and on the menu.

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 17:22:19

sp_changeobjectowner

Tara

jmcbride
Starting Member

24 Posts

Posted - 2004-06-02 : 15:15:17

I changed the object owner but haven't been able to get the results I'm looking for. Here is an expanded example of what I'd like to work for:



STARTING WITH THIS DATA:
Name  EID  Title       Department
ROMO  123  RESEARCHER  BIC
ROMO  123  PROFESSOR   SOC
ROMO  123  RESEARCHER  SOC
ROMO  123  RESEARCHER  WHI

*EID is VARCHAR(8), others are VARCHAR(255)

ENDING WITH THIS:
Name  EID  Title           Department
ROMO  123  RESEARCHER<br>  BIC<br>
           PROFESSOR       SOC<br>
                           WHI

*The "<br>" make it easy for Web display.

Thanks again!

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources