Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join data

Author  Topic 

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-27 : 23:50:16
I have the following 3 tables

Select * from #i

ID EventID EventExtraDataID Path FileName CameraNumber CameraName
1 1014318 1091745 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg 0001014318_0_1.jpg 1 TV CH 1
2 1014318 1091746 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg 0001014318_0_2.jpg 1 TV CH 1
3 1014318 1091747 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg 0001014318_0_3.jpg 1 TV CH 1
4 1014318 1091754 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg 0001014318_1_1.jpg 2 Cam 2
5 1014318 1091755 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg 0001014318_1_2.jpg 2 Cam 2
6 1014318 1091756 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg 0001014318_1_3.jpg 2 Cam 2


Select * from #ib


ID FileName Image EventID EventExtraDataID CameraNumber
1 0001014318_0_1.jpg 0xFFD 1014318 1091745 1
2 0001014318_0_2.jpg 0xFFD 1014318 1091746 1
3 0001014318_0_3.jpg 0xFFD 1014318 1091747 1
4 0001014318_1_1.jpg 0xFFD 1014318 1091754 2
5 0001014318_1_2.jpg 0xFFD 1014318 1091755 2
6 0001014318_1_3.jpg 0xFFD 1014318 1091756 2

Select * from #t


SiteID SiteName EventID EventType Description Cause SourceID SourceType LocalStartTime EventStartTime LocalEndTime EventEndTime Indicator ZoneNumber EventCode ZoneText DetectorText RID Media Format YearPath MonthPath DayPath EventExtraDataID FileName Type Format StartTime EndTime CameraNumber CameraName LoginName Comments CommentedTime
21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091745 0001014318_0_1.jpg Image HYIMAGES 2013-02-22 09:50:45.000 2013-02-22 09:50:45.000 1 TV CH 1 NULL NULL NULL
21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091746 0001014318_0_2.jpg Image HYIMAGES 2013-02-22 09:50:50.000 2013-02-22 09:50:50.000 1 TV CH 1 NULL NULL NULL
21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091747 0001014318_0_3.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 1 TV CH 1 NULL NULL NULL
21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091754 0001014318_1_1.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 2 Cam 2 NULL NULL NULL
21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091755 0001014318_1_2.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 2 Cam 2 NULL NULL NULL
21 Delta 1014318 EV_TYPE_HY_CAMERAS_CAM01_MOTION1 Quad Alarm CAM01 MOTION1 1 EV_SRC_HY_CAMERAS 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 2013-02-22 09:36:39.720 2013-02-22 09:50:45.000 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 1091756 0001014318_1_3.jpg Image HYIMAGES 2013-02-22 09:50:55.000 2013-02-22 09:50:55.000 2 Cam 2 NULL NULL NULL


I am looking for the following result. Basically there is only ever one event (EventID) but the event has two associated cameras in this example (there could be upto a max of 16 cameras, so in this case there would be 48 images, 16x3=48) and each camera has 3 images. I need the 3 images (image, filename and path fields) for each CameraNumber to be outer applied or something. The last 9 fields are the ones that need to be joined.

EventExtraDataID EventID FileName CameraNumber CameraName SiteID SiteName EventType Description Cause SourceID SourceType LocalStartTime EventStartTime LocalEndTime EventEndTime Indicator ZoneNumber EventCode ZoneText DetectorText RID Media Format YearPath MonthPath DayPath Type Format StartTime EndTime LoginName Comments CommentedTime FileName1 Image1 FileName2 Image2 FileName3 Image3 Path1 Path2 Path3
1091745 1014318 0001014318_0_1.jpg 1 TV CH 1 21 Delta CAM01 MOTION1 Quad Alarm CAM01 MOTION1 1 Cameras 2013-02-22 09:36:39 2013-02-22 09:50:45 2013-02-22 09:36:39 2013-02-22 09:50:45 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 Image HYIMAGES 2013-02-22 09:50:45 2013-02-22 09:50:45 NULL NULL NULL 0001014318_0_1.jpg 0xFFD8 0001014318_0_2.jpg 0xFFD8 0001014318_0_3.jpg 0xFFD8 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg
1091747 1014318 0001014318_1_1.jpg 2 Cam 2 21 Delta CAM01 MOTION1 Quad Alarm CAM01 MOTION1 1 Cameras 2013-02-22 09:36:39 2013-02-22 09:50:45 2013-02-22 09:36:39 2013-02-22 09:50:45 Activate Z000 11034 SYSTEM CAM01 MOTION1 NULL HYIMAGES 2013 2 22 Image HYIMAGES 2013-02-22 09:50:45 2013-02-22 09:50:45 NULL NULL NULL 0001014318_1_1.jpg 0xFFD8 0001014318_1_2.jpg 0xFFD8 0001014318_1_3.jpg 0xFFD8 J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg


I have the following code that worked prior when there was only ever one camera and therefore 3 images. Can this be modified to provide the 2 required rows?


Select DISTINCT #t.EventExtraDataID, #t.EventID, #t.FileName, #t.[CameraNumber], #t.[CameraName], [SiteID], [SiteName], dbo.EventTypeConv ([EventType]) As [EventType], [Description], [Cause], [SourceID], dbo.SourceTypeConv ([SourceType]) As [SourceType], CONVERT(VARCHAR(19), [LocalStartTime], 120) AS [LocalStartTime], CONVERT(VARCHAR(19), [EventStartTime], 120) AS [EventStartTime], CONVERT(VARCHAR(19), [LocalEndTime], 120) AS [LocalEndTime], CONVERT(VARCHAR(19), [EventEndTime], 120) AS [EventEndTime], [Indicator], [ZoneNumber], [EventCode], [ZoneText], [DetectorText], [RID], [Media Format], [YearPath], [MonthPath], [DayPath], [Type], [Format], CONVERT(VARCHAR(19), [StartTime], 120) AS [StartTime], CONVERT(VARCHAR(19), [EndTime], 120) AS [EndTime], [LoginName], [Comments], CONVERT(VARCHAR(19), [CommentedTime], 120) AS [CommentedTime],
i1.FileName AS FileName1, i1.Image AS Image1,
i2.FileName AS FileName2, i2.Image AS Image2,
i3.FileName AS FileName3, i3.Image AS Image3,
i4.Path As Path1,
i5.Path As Path2,
i6.Path As Path3
From #t
LEFT JOIN
#ib AS i1 ON #t.FileName = i1.FileName
OUTER APPLY (Select FileName, Image From #ib Where ID = 2) AS i2
OUTER APPLY (Select FileName, Image From #ib Where ID = 3) AS i3
OUTER APPLY (Select Path From #i Where ID = 1) AS i4
OUTER APPLY (Select Path From #i Where ID = 2) AS i5
OUTER APPLY (Select Path From #i Where ID = 3) AS i6
Where ID = 1
ORDER BY #t.EventID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 00:23:48
post the sample data between code tags and properly formatted. also provide it as insert statements so that its easier for us to create it and see for ourselves. then give sample output you desire.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-28 : 01:38:20
This case has 2 cameras associated with the event and each camera will always have 3 images.

The number of cameras associated with an event could be upto 16. In this case there would be 16 rows required (one for each camera) each with the 3 FileName, Image and Path fields.


CREATE TABLE #i (ID int IDENTITY(1,1) NOT NULL, [EventID] int, [EventExtraDataID] int, [Path] nvarchar(255), [FileName] nvarchar(max), [CameraNumber] int, [CameraName] nvarchar(max))
INSERT INTO #i ([EventID], [EventExtraDataID], [Path], [FileName], [CameraNumber], [CameraName])
Values
('1014318', '1091745', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg', '0001014318_0_1.jpg', '0', 'TV CH 1'),
('1014318', '1091746', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg', '0001014318_0_2.jpg', '0', 'TV CH 1'),
('1014318', '1091747', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg', '0001014318_0_3.jpg', '0', 'TV CH 1'),
('1014318', '1091754', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg', '0001014318_1_1.jpg', '1', 'Cam 2'),
('1014318', '1091755', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg', '0001014318_0_2.jpg', '1', 'Cam 2'),
('1014318', '1091756', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg', '0001014318_0_3.jpg', '1', 'Cam 2')


CREATE TABLE #ib (ID int IDENTITY(1,1) NOT NULL, [FileName] nvarchar(255) NOT NULL, [Image] nvarchar(255), [EventID] int, [EventExtraDataID] int)
INSERT INTO #ib ([FileName], [Image], [EventID], [EventExtraDataID])
Values
('0001014318_0_1.jpg', '0xFFD', '1014318', '1091745'),
('0001014318_0_2.jpg', '0xFFD', '1014318', '1091746'),
('0001014318_0_3.jpg', '0xFFD', '1014318', '1091747'),
('0001014318_1_1.jpg', '0xFFD', '1014318', '1091754'),
('0001014318_1_2.jpg', '0xFFD', '1014318', '1091755'),
('0001014318_1_3.jpg', '0xFFD', '1014318', '1091756')

Create Table #t (SiteName nvarchar(50), EventType nvarchar(50), EventID int, EventExtraDataID int, [FileName] nvarchar(50), CameraNumber int, CameraName nvarchar(50))
INSERT INTO #t ([SiteName], [EventType], [EventID], [EventExtraDataID], [FileName], [CameraNumber], [CameraName])
Values
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091745', '0001014318_0_1.jpg', '0', 'TV CH 1'),
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091746', '0001014318_0_2.jpg', '0', 'TV CH 1'),
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091747', '0001014318_0_3.jpg', '0', 'TV CH 1'),
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091754', '0001014318_1_1.jpg', '1', 'Cam 2'),
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091755', '0001014318_1_2.jpg', '1', 'Cam 2'),
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1091756', '0001014318_1_3.jpg', '1', 'Cam 2')



--Expected Result *** Note Image is normally varbinary(max) and I reduce the fields in #t to make it a little cleaner.***
Create Table #Expected_Result (SiteName nvarchar(50), EventType nvarchar(50), EventID int, CameraNumber int, CameraName nvarchar(50), FileName1 nvarchar(255), Image1 nvarchar(255), Path1 nvarchar(255), FileName2 nvarchar(255), Image2 nvarchar(255), Path2 nvarchar(255), FileName3 nvarchar(255), Image3 nvarchar(255), Path3 nvarchar(255))
INSERT INTO #Expected_Result
Values
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '0', 'TV CH 1', '0001014318_0_1.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_1.jpg', '0001014318_0_2.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_2.jpg', '0001014318_0_3.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_0_3.jpg'),
('Delta', 'EV_TYPE_HY_CAMERAS_CAM01_MOTION1', '1014318', '1', 'Cam 2', '0001014318_1_1.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_1.jpg', '0001014318_1_2.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_2.jpg', '0001014318_1_3.jpg', '0xFFD', 'J:\VCP DB 3\VCPDataRoot\EventExtraData\21\2013\2\22\0001014318_1_3.jpg')

Select * From #i
Select * From #ib
Select * From #t
Select * From #Expected_Result

Drop Table #i
Drop Table #ib
Drop Table #t
Drop Table #Expected_Result
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:31:21
[code]
select SiteName,EventType,EventID,CameraNumber,CameraName,
MAX(CASE WHEN Seq=1 THEN FileName END) AS Filename1,
MAX(CASE WHEN Seq=1 THEN Image END) AS Image1,
MAX(CASE WHEN Seq=1 THEN Path END) AS Path1,
MAX(CASE WHEN Seq=2 THEN FileName END) AS Filename2,
MAX(CASE WHEN Seq=2 THEN Image END) AS Image2,
MAX(CASE WHEN Seq=2 THEN Path END) AS Path2,
MAX(CASE WHEN Seq=3 THEN FileName END) AS Filename3,
MAX(CASE WHEN Seq=3 THEN Image END) AS Image3,
MAX(CASE WHEN Seq=3 THEN Path END) AS Path3
from
(
select ROW_NUMBER() OVER (PARTITION BY i.EventID,i.CameraNumber,i.CameraName ORDER BY i.EventExtraDataID) AS Seq,SiteName,EventType,i.EventID,i.CameraNumber,i.CameraName,ib.[FileName],ib.Image,i.Path
from #i i
inner join #ib ib
ON ib.EventID = i.EventID
and ib.EventExtraDataID = i.EventExtraDataID
inner join #t t
on t.EventExtraDataID = i.EventExtraDataID
and t.EventID = i.EventID
)t
group by SiteName,EventType,EventID,CameraNumber,CameraName
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:32:07
to make it dynamic use

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-03-03 : 18:28:01
Thanks visakh16.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-03 : 22:25:07
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -