How to display result of a query in one row when joined with another table

Question

I tried searching but I couldn't find a solution. I am trying to join two tables and basically table 2 has multiple rows for one row from table 1. But while displaying, I want to list all columns from table 1 along with all fields from table 2 but in one single line. That is table 2 rows will be displayed as columns.

Table 1

ID  Col2    Col3    Col4
1   John    Smith   CA
2   Henry   Bond    FL

Table 2

ID (FK) Type    Value
1   Car1        Honda
1   Car2        Toyota
2   Car1        Honda
2   Car2        Nissan
3   Car3        Audi

Result should be

ID  Col2    Col3    Col4    Car1    Car2    Car3
1   John    Smith   CA      Honda   Toyota  
2   Henry   Bond    FL      Honda   Nissan  Audi

What is the most # of cars a person has? Do you care if the cars are aggregated in one result column? In the latter case you can use group_concat (if mysql) listagg (if Oracle) string_agg (if postgresql) etc. — Brian DeMilia, Jul 16 '14 at 22:49
Thanks for the comments people. @Brian DeMilia, no, I don't want it to be aggregated. In fact, I want the columns to be generated dynamically. Database is SQL Server 2012 — Andy, Jul 17 '14 at 01:41

score 0 · Accepted Answer · answered Jul 16 '14 at 23:18

0

Are you after something like this?

SQL Fiddle: http://sqlfiddle.com/#!6/a379d/2

create table people
(
  id bigint not null identity(1,1) primary key
  , firstName nvarchar(32)
  , lastName nvarchar(32)
  , state nchar(2)
)
create table personCar
(
  id bigint not null identity(1,1) primary key
  , personId bigint not null
  , carType nvarchar(32)
  , carModel nvarchar(32)
)

insert people select 'John', 'Smith', 'CA'
insert people select 'Henry', 'Bond', 'FL'

insert personCar select 1, 'Car1', 'Honda'
insert personCar select 1, 'Car2', 'Toyota'
insert personCar select 2, 'Car1', 'Honda'
insert personCar select 2, 'Car2', 'Nissan'
insert personCar select 2, 'Car3', 'Audi'


select firstName, lastName, state
, [Car1], [Car2], [Car3]
from 
(
  select firstName, lastName, state, carModel, carType
  from people p
  inner join personCar c
  on c.personId = p.id
) x
pivot 
(
  max(carModel) for carType in ([Car1], [Car2], [Car3])
) pvt

answered Jul 16 '14 at 23:18

JohnLBevan

22,735
13
96
178

Thanks JohnLBevan. I already got the above query. But I need the columns to be generated dynamically, as in if I add another car (Car4) for Henry, Car4 should be displayed as a separate column. I think I have an idea and will try it out and let you know. – Andy Jul 17 '14 at 01:43
Actually I got the code to get to work where the columns will get generated dynamically. Now the problem is I need to call this stored procedure from another query. How can I do that? I can't create user defined function with return value table as the columns are dynamic. – Andy Jul 17 '14 at 02:16
No worries. You could use `OpenRowset` for that; but it's painful: http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx – JohnLBevan Jul 17 '14 at 08:46
Alternatively (slightly nicer, but still not great) there's the `insert * into #temp from exec` method: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure – JohnLBevan Jul 17 '14 at 08:49

How to display result of a query in one row when joined with another table

1 Answers1