How to Show Details Records in a column ?

[essa2000]

Dears ;

I want to show the details of a master in a column and each items should be seperated with comma. For example: I have two tables Countries and States, CountryID is foreign key in states table so data is as follows.

CountryID Country
1 U.S.A.
2 Pakistan

StateID CountryID State
1 1 California
2 1 Illinois
3 1 Washington
4 2 Sind
5 2 Punjab
6 2 N.W.F.P


Now I want to show the data like this:

Country States
U.S.A California, Illinois, Washington
Pakistan Sind, Punjab, N.W.F.P

I have done it using cursor but I don't want to use cursor and I need a query which solve this problem.

Help will be appriciated.


Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[wengyan]

I establish enviroment to test
CREATE TABLE Countries
(
CountryID int identity NOT NULL,
Country varchar(50),
CONSTRAINT PK_Countries PRIMARY KEY(CountryID)
)

CREATE TABLE States
(
StateID int identity NOT NULL ,
CountryID int NOT NULL,
State VARCHAR(60),
CONSTRAINT PK_States PRIMARY KEY(StateID)
)

INSERT INTO Countries(Country) VALUES('U.S.A.')
INSERT INTO Countries(Country) VALUES('Pakistan')

INSERT INTO States(CountryID,State) VALUES(1,'California')
INSERT INTO States(CountryID,State) VALUES(1,'Illinois')
INSERT INTO States(CountryID,State) VALUES(1,'Washington')

INSERT INTO States(CountryID,State) VALUES(2,'Sind')
INSERT INTO States(CountryID,State) VALUES(2,'Punjab')
INSERT INTO States(CountryID,State) VALUES(2,'N.W.F.P')

my solution:

SET NOCOUNT ON
CREATE TABLE #TMP
( CountryID int,
Country varchar(50),
States varchar(500)
)

DECLARE cu_States CURSOR FOR
SELECT dbo.States.CountryID, dbo.Countries.Country, dbo.States.State
FROM dbo.Countries INNER JOIN
dbo.States ON dbo.Countries.CountryID = dbo.States.CountryID
ORDER BY dbo.States.CountryID
FOR READ ONLY
DECLARE @cid int,@cty varchar(50),@state varchar(60)
DECLARE @oid int

OPEN cu_States
FETCH NEXT FROM cu_States INTO @cid, @cty, @state
SELECT @oid=0
WHILE @@FETCH_STATUS = 0
BEGIN
IF @cid<>@oid
BEGIN
INSERT INTO #TMP VALUES(@cid,@cty,@state)
SELECT @oid=@cid
END
ELSE
BEGIN
UPDATE #TMP SET States=States+','+@state WHERE CountryID=@cid
END

FETCH NEXT FROM cu_States INTO @cid, @cty, @state
END

CLOSE cu_States
DEALLOCATE cu_States

SELECT * FROM #TMP

DROP TABLE #TMP

SET NOCOUNT OFF

The Result is :

CountryID Country States
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 U.S.A. California,Illinois,Washington
2 Pakistan Sind,Punjab,N.W.F.P
WENG YAN

 

[wengyan]

You also can find at FAQ
(Concatenating a one-to-many relationship into a single column )
faq183-2146 WENG YAN

 

[essa2000]

Dear ;

I have already done it with cursor but as I wrote in my post I need a single query to acheive this task and I don't want to use cursor. I appreciate your help too. Someone can do this with one query.
Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[tlbroadbent]

Essa,

What do you mean by a single query? One select statement? This cannot be done in a single select statement without imposing some severe restrictions or perhaps restructuring the tables.

Check out my recent reply to a similar question in thread183-369884. I didn't use a cursor but I also didn't use a single select statement. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[essa2000]

Dear Terry ;

Thanks for it. I have seen that thread and now I have a question for you that. What is the difference in using Cursor and using While loop from a temp table in SQL statements.

I have checked it and temp table loop and cursors both were
taking the same time in giving results.

Please , explain the difference.

Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[tlbroadbent]

There isn't a lot of difference between the scripts. Both use WHILE looping. Both utilize a temp table to create the final result set.

One script processes rows by country code. The other processes by row number created in the temp table.

It is not surprising that the performance is similar. The same data is being processed.

Which script you choose will depend on personal preference and how well you understand the scripts. I present my script as an alternate method but not necessarily a better method in all cases. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[SQLSister]

One speed differnce between using a temp table and looping through it vice using a cursor is that you can usually limit the contents of a temp table to a smaller set than the original table so you are looping through fewer records thus going faster than a cursor on the main table.