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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2015 at 12:49 am
Quick suggestion: Use dynamic crosstab:
-- Prepare Sample Data
CREATE TABLE INCIDENT(
ID INT PRIMARY KEY,
[Date] DATE
);
CREATE TABLE PEOPLE(
INC_ID INT FOREIGN KEY REFERENCES INCIDENT(ID),
PersonIteration INT,
Complaint VARCHAR(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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply