Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005

    Unanswered: Is the following possible through SQL?

    Hi everyone,

    on a car renting company program, I have 3 tables, Persons, Cars, PersonRentsCar

    Persons - Cars is obviously a m:n relation.

    What I want to get is a Grid containing only once every name and in separate cells the cars he/she has already rented, something like:
    Person1 Car1 Car3 Car34
    Person3 Car2 Car3 Car23
    Person17 Car 2 Car7 Car34 Car56

    I think that this is impossible through SQL. But, is there maybe a way through a function to do that? Or does it need an extern programme, which will get and sort the data in a List or Array?

    I am almost convinced that I need a separate small utility to do the job, but I wanted to ask here, in case someone has a better idea.

    Thanks a lot in advance.
    Last edited by nst2; 01-19-12 at 04:22.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada

  3. #3
    Join Date
    Sep 2005


    Thanks a lot for your reply.

  4. #4
    Join Date
    Sep 2005
    I read the tutorials, did the examples, also tried pivot in Excel too, but still couldn't manage to have the desired result.

    As I read, PIVOT is being used in order to get numerical equations first, like MAX, SUM etc.

    But what I need is from a view like the following:

    Person17 Car34
    Person1 Car1
    Person1 Car3
    Person3 Car3
    Person1 Car34
    Person3 Car2
    Person17 Car2
    Person3 Car23
    Person17 Car7
    Person17 Car56

    to get a table like that:

    Person1 Car1 Car3 Car34
    Person3 Car2 Car3 Car23
    Person17 Car2 Car7 Car34 Car56

    without any numerical functions

  5. #5
    Join Date
    Nov 2004
    Provided Answers: 4
    Try this:
    SELECT dt1.person,
    	(SELECT LTRIM(RTRIM(car)) + ' '
    	FROM PersonRentsCar as dt2
    	WHERE dt2.Person = dt1.Person
    	ORDER BY car
    	FOR XML PATH('')
    	) AS Cars
    FROM PersonRentsCar as dt1
    GROUP BY dt1.Person ;
    Last edited by Wim; 01-21-12 at 06:41.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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