October 11, 2015 at 12:48 pm
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?
October 11, 2015 at 12:56 pm
kdefilip (10/11/2015)
HiI 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 12, 2015 at 12:49 am
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
October 12, 2015 at 1:09 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 12, 2015 at 1:31 am
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.
October 12, 2015 at 2:04 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply