Results 1 to 13 of 13

Thread: Select problem

  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Select problem

    How

    TABLE1
    Capital Country
    --------- ----------
    Paris France
    London England
    Washington USA


    TABLE2
    Capital Person
    -------- ---------
    Paris Chirac
    Paris Sarkozy
    Paris Depardieu
    London Starksky
    London Bauer
    Washington Bush



    How do I get for one given Country this result :

    Country Persons
    --------- ------------
    France Chirac Sarkozy Depardieu

  2. #2
    Join Date
    Dec 2003
    Posts
    9
    Try:

    select table1.capital, table2.person
    from table1 join table2

    on table1.country = table2.country

    If you have a look at BOL and 'JOINS' this will explain it better and fuller.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    nope, joins will return 3 rows :

    France Sarkozy
    France Depardieu
    France Chirac



    and I need 1 row :

    France Sarkozy, Depardieu, Chirac

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The only ways to do this are to loop through the recordset building a result string, or if you have some sort of identifier indicating the type of name (President, VicePresident, Ambassdor...) you can create a crosstab with a colum for each identifier. You could also use the ordinal values of your records as column headers.

    Actually, I think there is a third method that uses a table of sequential numbers.

    blindman

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    just for info in Sybase
    we had the aggregate function LIST

  6. #6
    Join Date
    Dec 2003
    Posts
    5

    Use User Defined Function

    Hi
    U can create the User Defined function which will return u the persons name

    Create Function fx_PersonList (@Country Varchar(100))
    RETURNS Varchar(2000)
    As
    Begin
    Declare @Person_List Varchar(2000)

    -- here person's will added and the (,) delimeter
    Select @Person_List = coalesce(@Person_List +',','') + T1.Person From
    Table2 T1, Table1 T2
    Where
    T2.Country=@Country and
    T1.Capital=T2.Captial


    End


    in the Query u can have

    Select Capital, fx_PersonList(Country) From
    Country

    Here u can get the result

    Bye

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Use User Defined Function

    I think it will resolve my problem
    Thx for the function

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you looking for

    [sniped]

    DECLARE @x varchar(8000), @y varchar(10)

    SELECT @x = '', @y = 'FRANCE'

    SELECT @x = @x + Person
    FROM Table1 a INNER JOIN Table2 b ON a.Capital = b.Capital
    WHERE a.country = 'France'

    SELECT @y+@x

    ???
    [/sniped]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good solution, thivya. I didn't know that Coalesce could span records. The example in BOL only shows it operating on a single record.

    blindman

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Good solution, thivya. I didn't know that Coalesce could span records. The example in BOL only shows it operating on a single record.

    blindman
    It's not Coalesce, it's the re-assignment of variable to itself..

    Coalesce just allows you to make sure you dont get a comma comma

    Brett,blindman,,Karolyn

    I was shocked when I saw it originally...still don't understand how you can flip rows on their side like that...

    Same thing with

    USE Northwinf
    GO
    SELECT @x = OrderId FROM Orders

    Gets the last one...DB2 would exploded with a -811 SQLCondCode...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the coalesce is there to ensure that the list doesn't start with a comma

    if there's a row where Person is null, then you might actually get two consecutive commas

    uh oh, wait a sec...

    if Person is null then concatenating it to coalesce(@Person_List +',','') will result in null, with the result that for the next row, @person_list will be null...

    resulting in the stuff at the front of the list getting lost?

    i dunno, i don't have sql server to test on -- buehler? anyone?

    rudy

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for anyone interested, here's the Sybase LIST function

    if only microsoft would implement something similar...


    rudy

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Blows my mind.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •