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

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:Archimedes 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

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

wrote in message
news:1192806078.033973.75640@t8g2000prg.googlegroups.com...
> 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:Archimedes 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
>


You didn't explain what determines the ordering of each pair of names.
There's nothing in the table to tell us that so I've just combined MIN and
MAX to return the ordering you say you want.

SELECT
MAX(CASE WHEN Type = 'P' THEN Person END) p1,
MIN(CASE WHEN Type = 'P' THEN Person END) p2,
MIN(CASE WHEN Type = 'P' THEN Country END) c1,
MAX(CASE WHEN Type = 'P' THEN Country END) c2,
MAX(CASE WHEN Type = 'Q' THEN Person END) p3,
MIN(CASE WHEN Type = 'Q' THEN Person END) p4,
MIN(CASE WHEN Type = 'Q' THEN Country END) c3,
MAX(CASE WHEN Type = 'Q' THEN Country END) c4,
MAX(CASE WHEN Type = 'W' THEN Person END) p5,
MIN(CASE WHEN Type = 'W' THEN Person END) p6,
MIN(CASE WHEN Type = 'W' THEN Country END) c5,
MAX(CASE WHEN Type = 'W' THEN Country END) c6
FROM TableA;


--
David Portas

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

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

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

(thetaamommy@gmail.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx