Solved

In SQL Server 2000, how do I write sql to display grouped data in one row (Similar to how the Stuff function works in 05)

Posted on 2009-07-15
6
272 Views
Last Modified: 2012-05-07
I have a sql statement which groups on department and returns:

Accounting    Ed
Accounting    Mike
Accounting    Peter
Sales    Janet
Sales    Susan

My question is, is it possible in sql (sql server 2000) to return the following:

Accounting    Ed,Mike,Peter
Sales    Janet,Susan


It doesnt have to be seperated by a comma, i just need to know what the technique is to return all the names on the same line as the department.

Here is what the SQL is:

Select Department, Name
From Records
group by Department, Name
0
Comment
Question by:BostonMA
  • 4
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24860116
Yes.. Its possible..
Just follow this example which I did before long time to get it done
--Sample table schema

Create table SchemaID

(

[ID] smallint,

SchemaID int NOT NULL

)

go
 

--Dummy insert statements

Insert into SchemaID values (1,12)

Insert into SchemaID values (1,13)

Insert into SchemaID values (1,14)
 

Insert into SchemaID values (2,15)

Insert into SchemaID values (2,16)

Insert into SchemaID values (2,17)

Insert into SchemaID values (2,18)
 

CREATE TABLE #temp_result ( a varchar(20), b varchar(1000));
 

Declare @id_con varchar(100);

DECLARE @id_in varchar(100);

DECLARE loop_val CURSOR FOR

SELECT DISTINCT cast(id AS varchar(20))

FROM SchemaID;
 

OPEN loop_val;
 

FETCH NEXT FROM loop_val

INTO @id_in;
 

WHILE @@FETCH_STATUS = 0

BEGIN

Select @id_con=Coalesce(@id_con + ', ', '') + Cast(SchemaID AS varchar(5)) From SchemaID Where [ID] = @id_in

INSERT INTO #temp_result VALUES (@id_in, @id_con);

SET @id_con = NULL;

FETCH NEXT FROM loop_val

INTO @id_in;

END
 

CLOSE loop_val;

DEALLOCATE loop_val;
 

SELECT * FROM #temp_result
 

-- Drop table once you fetch your Results

DROP TABLE #temp_result

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24860125
ID in my example is your Department and Name combination.
Just try yourself once with the code sample and if you face any errors, ready to help you...
0
 
LVL 4

Author Comment

by:BostonMA
ID: 24860593
I get the idea of your solution, and i could probably make it work given enough time, however at the present I dont have security access to create a table. Do you know of any other ways which dont involve creating a temp table?  

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24861415
Then I would suggest you to go for table variable which is not available in 2000.
Hence no other approach strikes in my mind for 2000 other than temp table.
0
 
LVL 4

Author Closing Comment

by:BostonMA
ID: 31603768
Thanks.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24865578
Welcome and glad to help you out..
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now