Moving column data to row - SQL Server Forums

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)

Moving column data to row

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-02-09 : 21:58:50

I have the following procedure and I would like the rows that are for the same Event/Blog ID to have the Image and FileName2 data on the same row. The FileName2 field is 0000384105_1.jpg (10 digits_1.jpg, 10 digits_2.jpg, 10 digits_3.jpg) Some events may have only one image or NULL for Image and FileName2. So currently it looks like this:

SiteID SiteName TransmitterType EventID/BlogID CameraNumber CameraName 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 LoginName Comments CommentedTime EventCommentsID SessionDetailsSessionID Image FileName2

3 Bravo V3100 384104 7 Camera 7 Duress Alarm CAM07 EVENT 7 Cameras 2011-02-10 13:01:02 2011-02-10 12:58:59 2011-02-10 13:01:02 2011-02-10 12:58:59 Activate Z000 11224 SYSTEM CAM07 EVENT NULL NULL 2011 2 10 NULL No_Images.png NULL NULL NULL NULL NULL NULL NULL NULL 246737 0x89... No_Images.png

3 Bravo V3100 384105 7 Camera 7 Duress Alarm CAM07 EVENT 7 Cameras 2011-02-10 13:01:02 2011-02-10 12:58:59 2011-02-10 13:01:02 2011-02-10 12:58:59 Activate Z000 11224 SYSTEM CAM07 EVENT NULL HYIMAGES 2011 2 10 567603 0000384105_1.jpg Image HYIMAGES 2011-02-10 12:58:59 2011-02-10 12:58:59 NULL NULL NULL NULL 246737 0xFF... 0000384105_1.jpg

3 Bravo V3100 384105 7 Camera 7 Duress Alarm CAM07 EVENT 7 Cameras 2011-02-10 13:01:02 2011-02-10 12:58:59 2011-02-10 13:01:02 2011-02-10 12:58:59 Activate Z000 11224 SYSTEM CAM07 EVENT NULL HYIMAGES 2011 2 10 567604 0000384105_2.jpg Image HYIMAGES 2011-02-10 12:59:00 2011-02-10 12:59:00 NULL NULL NULL NULL 246737 0xFF... 0000384105_2.jpg

3 Bravo V3100 384105 7 Camera 7 Duress Alarm CAM07 EVENT 7 Cameras 2011-02-10 13:01:02 2011-02-10 12:58:59 2011-02-10 13:01:02 2011-02-10 12:58:59 Activate Z000 11224 SYSTEM CAM07 EVENT NULL HYIMAGES 2011 2 10 567605 0000384105_3.jpg Image HYIMAGES 2011-02-10 12:59:00 2011-02-10 12:59:00 NULL NULL NULL NULL 246737 0xFF... 0000384105_3.jpg

NULL NULL NULL 5 NULL NULL USER NOTE USER NOTE 5 User 2011-02-09 12:41:28 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL a un9/02/2011 12:41 PM 2011-02-09 12:41:28 NULL NULL NULL NULL

..and I need this:

SiteID SiteName TransmitterType EventID/BlogID CameraNumber CameraName 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 LoginName Comments CommentedTime EventCommentsID SessionDetailsSessionID Image1 FileName1 Image2 FileName2 Image3 FileName3

3 Bravo V3100 384104 7 Camera 7 Duress Alarm CAM07 EVENT 7 Cameras 2011-02-10 13:01:02 2011-02-10 12:58:59 2011-02-10 13:01:02 2011-02-10 12:58:59 Activate Z000 11224 SYSTEM CAM07 EVENT NULL NULL 2011 2 10 NULL No_Images.png NULL NULL NULL NULL NULL NULL NULL NULL 246737 0x89... No_Images.png NULL NULL NULL NULL

3 Bravo V3100 384105 7 Camera 7 Duress Alarm CAM07 EVENT 7 Cameras 2011-02-10 13:01:02 2011-02-10 12:58:59 2011-02-10 13:01:02 2011-02-10 12:58:59 Activate Z000 11224 SYSTEM CAM07 EVENT NULL HYIMAGES 2011 2 10 567603 0000384105_1.jpg Image HYIMAGES 2011-02-10 12:58:59 2011-02-10 12:58:59 NULL NULL NULL NULL 246737 0xFF... 0000384105_1.jpg 0xFF... 0000384105_2.jpg 0xFF... 0000384105_3.jpg

NULL NULL NULL 5 NULL NULL USER NOTE USER NOTE 5 User 2011-02-09 12:41:28 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL a un9/02/2011 12:41 PM 2011-02-09 12:41:28 NULL NULL NULL NULL NULL NULL NULL NULL

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Site_Report] Script Date: 02/10/2011 12:59:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Site_Report]

@SiteID nvarchar(max),
--@EventID nvarchar(max),
@DateRange nvarchar(30),
@DBName nvarchar(max)

AS

SET NOCOUNT ON

Declare @File As nvarchar(255)
Declare @Path As nvarchar(255)
Declare @EventID1 As int
Declare @EventExtraDataID As int
Declare @NoImage As nvarchar(255)

Declare @StartDateRange As DateTime
Declare @EndDateRange As DateTime

SET @NoImage = (SELECT EntryValue + '\VCPRS\No_Images.png' FROM (SELECT EntryValue FROM VCSystemData WHERE EntryName = 'VCPDataRootPath') as t)
--Print @NoImage

SET @StartDateRange = (Select StartDateRange From fn_DateSel (@DateRange))
SET @EndDateRange = (Select EndDateRange From fn_DateSel (@DateRange))

Declare @sql1 nvarchar(max)
Declare @ParamDefinition1 AS nvarchar(max)
Select @sql1=''
Select @sql1=@sql1+ 'Select EventDetails.EventID, EventExtraDataID, ISNULL(EntryValue + ''\EventExtraData\'' + CAST(SiteID As nvarchar(3)) + ''\'' + CAST(DATEPART(yyyy, EventStartTime) As nvarchar(4)) + ''\'' + CAST(DATEPART(mm, EventStartTime)As nvarchar(2)) + ''\'' + CAST(DATEPART(dd, EventStartTime)As nvarchar(2)) + ''\'' + FileName, ''' +@NoImage+ ''') As [Path], ISNULL(FileName, ''No_Images.png'') As [FileName]
FROM '+name+'.dbo.EventDetails LEFT JOIN
'+name+'.dbo.EventExtraData ON '+name+'.dbo.EventDetails.EventID = '+name+'.dbo.EventExtraData.EventID LEFT JOIN
'+name+'.dbo.VCSystemData ON '+name+'.dbo.VCSystemData.EntryName = ''VCPDataRootPath''
WHERE EventDetails.LocalStartTime >= @StartDateRange AND EventDetails.LocalStartTime <= @EndDateRange
AND EventDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','',''))
' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition1 =
' @SiteID nvarchar (50),
@DBName nvarchar (100),
@StartDateRange DateTime,
@EndDateRange DateTime'

CREATE TABLE #i (ID int IDENTITY(1,1) NOT NULL, [EventID] int, [EventExtraDataID] int, [Path] nvarchar(255), [FileName] nvarchar(max))
INSERT INTO #i ([EventID], [EventExtraDataID], [Path], [FileName])

Execute sp_Executesql @sql1, @ParamDefinition1, @SiteID, @DBName, @StartDateRange, @EndDateRange
--Print (@sql1)

--Select * from #i
--Order by EventID

CREATE TABLE #ib (ID int IDENTITY(1,1) NOT NULL, [FileName] nvarchar(255) NOT NULL, [Image] varbinary(max), [EventID] int, [EventExtraDataID] int)

DECLARE insert_cursor CURSOR FAST_FORWARD FOR

Select [EventID], [EventExtraDataID], [Path], [FileName] from #i

OPEN insert_cursor

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

WHILE @@FETCH_STATUS = 0

BEGIN

--Print @Path
--Print @File

EXEC('INSERT INTO #ib ([EventID], [EventExtraDataID], [FileName], [Image])
SELECT ''' + @EventID1 + ''', ''' + @EventExtraDataID + ''', ''' + @File + ''', *
FROM (
SELECT *
FROM OPENROWSET(BULK ''' + @Path + ''', SINGLE_BLOB) As rs) As im')

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

END

CLOSE insert_cursor
DEALLOCATE insert_cursor

--Select * from #ib
--Order by EventID

Declare @sql2 AS nvarchar (max)
Declare @ParamDefinition2 AS nvarchar(max)
SELECT @sql2=''
SELECT @sql2=@sql2+ 'SELECT SiteID, NULL, NULL, SiteName, BlogID, SourceID, SourceType, EntryDate, EntryValue, ''SITE NOTE''
FROM '+name+'.dbo.SiteDetails INNER JOIN
'+name+'.dbo.Blog ON '+name+'.dbo.SiteDetails.SiteID = '+name+'.dbo.Blog.SourceID
WHERE Blog.EntryDate >= @StartDateRange AND Blog.EntryDate <= @EndDateRange
AND SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND IsDeleted = ''False''
AND SourceType = ''Site''

Union ALL

SELECT NULL, UserID, LoginName, NULL, BlogID, SourceID, SourceType, EntryDate, EntryValue, ''USER NOTE''
FROM '+name+'.dbo.UserDetails INNER JOIN
'+name+'.dbo.Blog ON '+name+'.dbo.UserDetails.UserID = '+name+'.dbo.Blog.SourceID
WHERE Blog.EntryDate >= @StartDateRange AND Blog.EntryDate <= @EndDateRange
AND IsDeleted = ''False''
AND SourceType = ''User''

' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition2 =
' @SiteID nvarchar (50),
@DBName nvarchar (100),
@StartDateRange DateTime,
@EndDateRange DateTime'

Create table #b([SiteID] int, [UserID] int,[LoginName] nvarchar(50), [SiteName] nvarchar(50), [BlogID] int, [SourceID] int, [SourceType] nvarchar(50), [EntryDate] DateTime, [EntryValue] nvarchar(max), [NoteType] nvarchar (50))
Insert into #b

Execute sp_Executesql @sql2, @ParamDefinition2, @SiteID, @DBName, @StartDateRange, @EndDateRange
---Print @sql

Declare @sql As nvarchar(max)
Declare @ParamDefinition AS nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT DISTINCT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType, EventDetails.EventID, EventDetails.CameraNumber + 1, EventDetails.CameraName, EventDetails.Description, EventDetails.Cause, ISNULL(EventDetails.SourceID, ''0'') As SourceID, EventDetails.SourceType, EventDetails.LocalStartTime, EventDetails.EventStartTime, EventDetails.LocalEndTime, EventDetails.EventEndTime, EventDetails.Indicator, EventDetails.ZoneNumber, EventDetails.EventCode, EventDetails.ZoneText, EventDetails.DetectorText, EventDetails.RID, EventExtraData.Format, DATEPART(yyyy, EventStartTime) AS YearPath, DATEPART(mm, EventStartTime) AS MonthPath, DATEPART(dd, EventStartTime) AS DayPath, EventExtraData.EventExtraDataID, ISNULL(EventExtraData.FileName, ''No_Images.png'') As FileName, EventExtraData.Type, EventExtraData.Format,EventExtraData.StartTime,EventExtraData.EndTime, UserDetails.LoginName, EventComments.Comments ,EventComments.CommentedTime, EventComments.EventCommentsID, SessionDetails.SessionID
FROM '+name+'.dbo.EventDetails INNER JOIN
'+name+'.dbo.SiteDetails ON '+name+'.dbo.EventDetails.SiteID = '+name+'.dbo.SiteDetails.SiteID LEFT JOIN
'+name+'.dbo.EventExtraData ON '+name+'.dbo.EventDetails.EventID = '+name+'.dbo.EventExtraData.EventID LEFT JOIN
'+name+'.dbo.EventComments ON '+name+'.dbo.EventComments.EventID = '+name+'.dbo.EventDetails.EventID LEFT JOIN
'+name+'.dbo.SessionDetails ON '+name+'.dbo.SessionDetails.SessionID = '+name+'.dbo.EventDetails.SessionID LEFT JOIN
'+name+'.dbo.OperatorAction ON '+name+'.dbo.OperatorAction.SessionID = '+name+'.dbo.SessionDetails.SessionID LEFT JOIN
'+name+'.dbo.UserDetails ON '+name+'.dbo.UserDetails.UserID = '+name+'.dbo.EventComments.UserID OR '+name+'.dbo.OperatorAction.UserID = '+name+'.dbo.UserDetails.UserID
WHERE ((EventDetails.LocalStartTime >= @StartDateRange) AND (EventDetails.LocalStartTime <= @EndDateRange))
AND EventDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','',''))
' from sys.databases where name='VC' or name like 'VCA%'

--EventDetails.EventID IN (Select Param From fn_MVParam ('''+@EventID+''','',''))

Set @ParamDefinition =
' @SiteID nvarchar (50),
@DBName nvarchar (100),
@StartDateRange DateTime,
@EndDateRange DateTime'

Create table #t([SiteID] int, [SiteName] nvarchar(50), [TransmitterType] nvarchar(50), [EventID] int, [CameraNumber] int, [CameraName] nvarchar(max), [Description] nvarchar(max), [Cause] nvarchar(max), [SourceID] int, [SourceType] nvarchar(max), [LocalStartTime] DateTime, [EventStartTime] DateTime, [LocalEndTime] DateTime, [EventEndTime] DateTime, [Indicator] nvarchar(max), [ZoneNumber] nchar(4), [EventCode] int, [ZoneText] nchar(20), [DetectorText] nchar(20), [RID] nchar(16), [Media Format] nvarchar (50), [YearPath] nvarchar(4), [MonthPath] nvarchar(2), [DayPath] nvarchar(2), [EventExtraDataID] int, [FileName] nvarchar(200),[Type] nvarchar(50),[Format] nvarchar(50),[StartTime] DateTime,[EndTime] DateTime, [LoginName] nvarchar (50), [Comments] nvarchar (max), [CommentedTime] DateTime, [EventCommentsID] int, [SessionDetailsSessionID] int)
Insert into #t

Execute sp_Executesql @sql, @ParamDefinition, @SiteID, @DBName, @StartDateRange, @EndDateRange

Select DISTINCT [SiteID], [SiteName], [TransmitterType], #t.EventID AS [EventID/BlogID], [CameraNumber], [CameraName], [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], #t.EventExtraDataID, #t.FileName, [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], [EventCommentsID], [SessionDetailsSessionID], [Image], #ib.FileName As FileName2
From #t
Left Join #ib ON #t.EventID = #ib.EventID AND #t.FileName = #ib.FileName

Union ALL

Select [SiteID], [SiteName], NULL, [BlogID], NULL, NULL, [NoteType], [NoteType], SourceID, [SourceType], CONVERT(VARCHAR(19), EntryDate, 120) AS [EntryDate], NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, [LoginName], [EntryValue], CONVERT(VARCHAR(19), EntryDate, 120) AS [EntryDate], NULL, NULL, NULL, NULL
From #b

Order BY LocalStartTime Desc

Drop Table #t

Drop table #i

Drop Table #ib

Drop Table #b

SET NOCOUNT OFF

RETURN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources