Is the following possible through SQL?

nst2 · #1 (**permalink**) 01-19-12, 02:40

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.

r937 · #2 (**permalink**) 01-19-12, 05:27

yes, it's possible using PIVOT

here's a tutorial -- T-SQL PIVOT Statement - Pivoting (Crosstab) Data From a Database Table - T-SQL (Transact SQL) Tutorials

nst2 · #3 (**permalink**) 01-19-12, 09:24

Thanks a lot for your reply.

nst2 · #4 (**permalink**) 01-21-12, 03:41

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

Wim · #5 (**permalink**) 01-21-12, 05:32

Try this:

Code:

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 ;

Internet Services

Web Development

Digital Marketing

Consumer Tech