|
|
Posted by David Portas on 10/19/07 20:35
<thetaamommy@gmail.com> 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
[Back to original message]
|