February 16, 2016 at 1:14 pm
I have an issue where an occurrence (incident) can be categorized into multiple types. What I am trying to do is return the occurrence id and name along with a list of all the occurrence type's associated with it. The occurrence types associated are supposed to be concatenated together with some prefixed text at the start. Currently using a stored proc to do this but I'm not sure how to just return the types attached to the occurrence. Right now each row returned shows all occurrence types from the type table. I know that's basically what my select statement is doing. Just not sure if I'm needing a WHERE clause or if I should be joining it to another table (maybe I need to use a temp table?)
Here is what I have in my stored proc so far. Appreciate any help from the community.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.uspDataForSC
AS
BEGIN
DECLARE @IncidentTypeOutput varchar(max),@IncidentLocationOutput varchar(max)
SET @IncidentTypeOutput='The incident contained the following types: '
SELECT @IncidentTypeOutput=COALESCE(@IncidentTypeOutput + ' ', '')+ tblOccurrenceTypeTest.OccurrenceTypeName
FROM
dbo.tblOccurrenceTypeTest
END
SELECT
dbo.tblOccurrenceTest.Occurrence_ID,
dbo.tblOccurrenceTest.OccurrenceName,
@IncidentTypeOutput AS OccurrenceTypes
FROM
dbo.tblOccurrenceTest
INNER JOIN dbo.tblAssociatedTypesTest ON (dbo.tblOccurrenceTest.Occurrence_ID = dbo.tblAssociatedTypesTest.OccurrenceID)
INNER JOIN dbo.tblOccurrenceTypeTest ON (dbo.tblAssociatedTypesTest.OccurrenceTypeName = dbo.tblOccurrenceTypeTest.OccurrenceTypeName)
GROUP BY Occurrence_ID,OccurrenceName
And here are the 3 tables I am using
CREATE TABLE [dbo].[tblOccurrenceTest] (
[Occurrence_ID] int IDENTITY(1, 1) NOT NULL,
[OccurrenceName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblOccurence_tblOccurrenceTest] PRIMARY KEY CLUSTERED ([Occurrence_ID])
WITH (
PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO
/* Data for the 'dbo.tblOccurrenceTest' table (Records 1 - 5) */
INSERT INTO [dbo].[tblOccurrenceTest] ([Occurrence_ID], [OccurrenceName])
VALUES (1, N'Please unlock the door')
GO
INSERT INTO [dbo].[tblOccurrenceTest] ([Occurrence_ID], [OccurrenceName])
VALUES (2, N'Person needs assistance')
GO
INSERT INTO [dbo].[tblOccurrenceTest] ([Occurrence_ID], [OccurrenceName])
VALUES (3, N'Escort person to area')
GO
INSERT INTO [dbo].[tblOccurrenceTest] ([Occurrence_ID], [OccurrenceName])
VALUES (4, N'Tow Vehicle')
GO
INSERT INTO [dbo].[tblOccurrenceTest] ([Occurrence_ID], [OccurrenceName])
VALUES (5, N'Jump start vehicle')
GO
CREATE TABLE [dbo].[tblOccurrenceTypeTest] (
[OccurrenceTypeName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [tblOccurrenceTypeTest_pk] PRIMARY KEY CLUSTERED ([OccurrenceTypeName])
WITH (
PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [tblOccurrenceTypeTest_uq] UNIQUE ([OccurrenceTypeName])
WITH (
PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO
/* Data for the 'dbo.tblOccurrenceTypeTest' table (Records 1 - 7) */
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 1')
GO
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 2')
GO
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 3')
GO
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 4')
GO
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 5')
GO
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 6')
GO
INSERT INTO [dbo].[tblOccurrenceTypeTest] ([OccurrenceTypeName])
VALUES (N'Type 7')
GO
CREATE TABLE [dbo].[tblAssociatedTypesTest] (
[AsscoiateTypesTest_ID] int IDENTITY(1, 1) NOT NULL,
[OccurrenceTypeName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OccurrenceID] int NOT NULL,
CONSTRAINT [tblAssociatedTypesTest_pk] PRIMARY KEY CLUSTERED ([AsscoiateTypesTest_ID])
WITH (
PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO
/* Data for the 'dbo.tblAssociatedTypesTest' table (Records 1 - 6) */
INSERT INTO [dbo].[tblAssociatedTypesTest] ([AsscoiateTypesTest_ID], [OccurrenceTypeName], [OccurrenceID])
VALUES (1, N'Type 1', 1)
GO
INSERT INTO [dbo].[tblAssociatedTypesTest] ([AsscoiateTypesTest_ID], [OccurrenceTypeName], [OccurrenceID])
VALUES (2, N'Type 3', 1)
GO
INSERT INTO [dbo].[tblAssociatedTypesTest] ([AsscoiateTypesTest_ID], [OccurrenceTypeName], [OccurrenceID])
VALUES (3, N'Type 4', 1)
GO
INSERT INTO [dbo].[tblAssociatedTypesTest] ([AsscoiateTypesTest_ID], [OccurrenceTypeName], [OccurrenceID])
VALUES (4, N'Type 5', 2)
GO
INSERT INTO [dbo].[tblAssociatedTypesTest] ([AsscoiateTypesTest_ID], [OccurrenceTypeName], [OccurrenceID])
VALUES (5, N'Type 6', 2)
GO
INSERT INTO [dbo].[tblAssociatedTypesTest] ([AsscoiateTypesTest_ID], [OccurrenceTypeName], [OccurrenceID])
VALUES (6, N'Type 7', 2)
GO
February 16, 2016 at 1:33 pm
This article might be what you're looking for:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Here's an example based on that article:
SELECT
ot.Occurrence_ID,
ot.OccurrenceName,
STUFF( (SELECT ' ' + ott.OccurrenceTypeName
FROM dbo.tblOccurrenceTypeTest ott
JOIN dbo.tblAssociatedTypesTest att ON att.OccurrenceTypeName = ott.OccurrenceTypeName
WHERE ot.Occurrence_ID = att.OccurrenceID
FOR XML PATH('')), 1, 1, '')
FROM dbo.tblOccurrenceTest ot;
Stop using 3-part column names in your queries. It's a deprecated feature and it also makes the code longer and more difficult to read. Use table alias instead.
February 16, 2016 at 10:13 pm
If there is a possibility that OccurrenceTypeName will contain any of what SQL Server considers XML characters (e.g. CHAR(1) through CHAR(32), CHAR(38), CHAR(60) or CHAR(62)) then you would make a small change to Luis' excellent solution like so:
SELECT
ot.Occurrence_ID,
ot.OccurrenceName,
STUFF( (SELECT ' ' + ott.OccurrenceTypeName
FROM dbo.tblOccurrenceTypeTest ott
JOIN dbo.tblAssociatedTypesTest att ON att.OccurrenceTypeName = ott.OccurrenceTypeName
WHERE ot.Occurrence_ID = att.OccurrenceID
FOR XML PATH(''), TYPE).value('.','varchar(8000)'), 1, 1, '')
FROM dbo.tblOccurrenceTest ot;
This will slow the query down a little but prevent things like spaces (CHAR(32)),for example from being displayed as in your result set.
-- Itzik Ben-Gan 2001
February 17, 2016 at 5:38 am
Alan.B (2/16/2016)
If there is a possibility that OccurrenceTypeName will contain any of what SQL Server considers XML characters (e.g. CHAR(1) through CHAR(32), CHAR(38), CHAR(60) or CHAR(62)) then you would make a small change to Luis' excellent solution like so:
SELECTot.Occurrence_ID,
ot.OccurrenceName,
STUFF( (SELECT ' ' + ott.OccurrenceTypeName
FROM dbo.tblOccurrenceTypeTest ott
JOIN dbo.tblAssociatedTypesTest att ON att.OccurrenceTypeName = ott.OccurrenceTypeName
WHERE ot.Occurrence_ID = att.OccurrenceID
FOR XML PATH(''), TYPE).value('.','varchar(8000)'), 1, 1, '')
FROM dbo.tblOccurrenceTest ot;
This will slow the query down a little but prevent things like spaces (CHAR(32)),for example from being displayed as in your result set.
I left that out intentionally. If the OP claimed to have that error it would mean he didn't read the article. 😉
February 17, 2016 at 3:07 pm
Thanks for both of your suggestions guys. They helped me out a lot. And yes I made sure to read and bookmark the article! Thx again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply