Join one to many

Question

Join one to many

kdefilip

Grasshopper

Points: 17
More actions
October 11, 2015 at 12:48 pm

#308785

Hi
I have two tables, INCIDENT and PEOPLE
INCIDENT has two fields, ID and Date
PEOPLE has three fields, INC_ID [foreign key to INCIDENT.ID], PersonIteration, Complaint
There is always only one incident, but there can be one or more people involved in each incident.
I'd like to retrieve incidents, but have it include information from all involved person on one line.
No matter how I have tried to join these tables, I am ending up with one record each for each person involved in an incident, like so
ID PersonIteration Complaint
1 1 Head
1 2 Neck
1 3 Shoulder
I would like to join in this fashion:
ID PersonIteration1 Complaint1 PersonIteration2 Complaint2
1 1 Head 2 Neck
Is there any way to do this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

kdefilip (10/11/2015)
Hi
I have two tables, INCIDENT and PEOPLE
INCIDENT has two fields, ID and Date
PEOPLE has three fields, INC_ID [foreign key to INCIDENT.ID], PersonIteration, Complaint
There is always only one incident, but there can be one or more people involved in each incident.
I'd like to retrieve incidents, but have it include information from all involved person on one line.
No matter how I have tried to join these tables, I am ending up with one record each for each person involved in an incident, like so
ID PersonIteration Complaint
1 1 Head
1 2 Neck
1 3 Shoulder
I would like to join in this fashion:
ID PersonIteration1 Complaint1 PersonIteration2 Complaint2
1 1 Head 2 Neck
Is there any way to do this?

There is no way to return varying numbers of columns in a query such as this.

However, you can return all of the information in a single column, using the FOR XML PATH technique which is exemplified here.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Felix Pamittan Mr or Mrs. 500 Points: 535 More actions · Answer 2

Quick suggestion: Use dynamic crosstab:

-- Prepare Sample Data

CREATE TABLE INCIDENT(

IDINT PRIMARY KEY,

[Date]DATE

);

CREATE TABLE PEOPLE(

INC_IDINT FOREIGN KEY REFERENCES INCIDENT(ID),

PersonIterationINT,

ComplaintVARCHAR(20)

);

INSERT INTO INCIDENT VALUES(1, GETDATE());

INSERT INTO PEOPLE VALUES (1, 1, 'Head'), (1, 2, 'Neck'), (1, 3, 'Shoulder');

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql =

'SELECT

ID' + CHAR(10)

SELECT @sql = @sql +

', MAX(CASE WHEN PersonIteration = ' + CONVERT(VARCHAR(10), n) + ' THEN PersonIteration END) AS ' + QUOTENAME('PersonIteration' + CONVERT(VARCHAR(10), n)) + CHAR(10) +

', MAX(CASE WHEN PersonIteration = ' + CONVERT(VARCHAR(10), n) + ' THEN Complaint END) AS ' + QUOTENAME('Complaint' + CONVERT(VARCHAR(10), n)) + CHAR(10)

FROM (

SELECT TOP(SELECT MAX(PersonIteration) FROM PEOPLE)

ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM sys.columns

)t(n)

SELECT @sql = @sql +

'FROM INCIDENT i

INNER JOIN People p

ON p.INC_ID = i.ID

GROUP BY i.id'

PRINT @sql

EXEC sp_executesql @sql

-- Clean up sample data

DROP TABLE PEOPLE

DROP TABLE INCIDENT

The result would look like this:

ID PersonIteration1 Complaint1 PersonIteration2 Complaint2 PersonIteration3 Complaint3

----------- ---------------- -------------------- ---------------- -------------------- ---------------- --------------------

1 1 Head 2 Neck 3 Shoulder

The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Nice code, Felix.

Quick suggestion...

Not sure how quick it was though; have you been listening to Eirikur?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Felix Pamittan Mr or Mrs. 500 Points: 535 More actions · Answer 4

Phil Parkin (10/12/2015)
Nice code, Felix.
Quick suggestion...
Not sure how quick it was though; have you been listening to Eirikur?

Yeah! I've been lurking this forum for quite some time now and decided to finally use what I've learned and contribute. I've been a fan of the regulars here especially Jeff, but yeah, that one's influenced by Eirikur.

The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

Felix Pamittan (10/12/2015)
Phil Parkin (10/12/2015)
Nice code, Felix.
Quick suggestion...
Not sure how quick it was though; have you been listening to Eirikur?
Yeah! I've been lurking this forum for quite some time now and decided to finally use what I've learned and contribute. I've been a fan of the regulars here especially Jeff, but yeah, that one's influenced by Eirikur.

Bravo and welcome.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel