Multiple rows combined into onerow and onecolumn separated by a special character

[thetaamommy]

Hi All :
CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), Subject
Varchar(20), Type Char(1))

INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')
INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')
INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')

INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')
INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')
INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')

SELECT * FROM TABLEA

I am on SQL 2000.
I need an output where i have to have a resultset grouped on Type, but
the results in one row.

In the resultset I need

TypeP Person Type P Country, Type Q Person, Type Q Country, Type
W Person Type W Country
---------------------------------------------------------------------------------------------------------------------
Einstein:Archim edes Germany:Greece Kant:Aristotle Germany:Greece
Kafka:Homer Germany:Greece
*************** *************** *************** *************** ***
I have written a puesdo-cursor code to do the same, but if there is a
way to do as a set operation, that would be great

Please select as a whole and past in query analyser as the resultset
is all overlaid when i paste in this box.
Thank you
RS

[thetaamommy]

Hi David :

Thank you much.
This gives me 2 values, min and max.
but, I will have a total of 4 persons on philosophy and it could be
either 0 or 1 or 2 or 3 or 4 persons in philisophy.
Is it possible to do a set operation for that ?

What i came up with is 4 sql set stmts.
first one is insert stmt of a random philisopher in temp table:

second will be update stmt and in where clause excluding what is
already in temp table
third stmt is another update to 3rd column excluding the two already
in temp table
and fourth stmt

Is there a better way ?
Thank you

[Erland Sommarskog]

(th*********@gm ail.com) writes:

Thank you much.
This gives me 2 values, min and max.
but, I will have a total of 4 persons on philosophy and it could be
either 0 or 1 or 2 or 3 or 4 persons in philisophy.
Is it possible to do a set operation for that ?

It could certainly help if you could produce a more extensive set of
sample data, also explain the logic to get the desired result.

But if I am guessing correctly what you are trying to achieve, the
answer is that on SQL 2000, your prospects are bleak, at as long as
there is no upper limit of how many persons there can be.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx