SQL Query HELP...
-
Would like some help please with a query. I have a table
CREATE TABLE [dbo].[t_Attachments] (
[AttachmentID] [int] IDENTITY (1, 1) NOT NULL ,
[MsgID] [int] NOT NULL ,
[FileName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[AttachmentContents] [image] NOT NULL
)
Select * form t_Attachments
AttachmentID MsgID FileName AttachmentContents
1 19 test.txt 0x48656C6C
2 19 test2.txt 0x48656C6C
Now what I would is to return in one row all the information relating to MsgID
MsgID AttachmentID FileName AttachmentContents
19 1, 2 test.txt, test2.tx 0x48656C6C, 0x48656C6C
Any ideas would be very much appreciated.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
ALTER FUNCTION ReturnAll (@msgID VARCHAR(8000) )
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result varchar(8000)
,@i int
,@one varchar(100)
,@two varchar(100)
,@three varchar(255)
SET @one = ''
SET @two = ''
SET @three = ''
SET @result = ''
SELECT @i = Count(*) FROM t_Attachments WHERE msgID = @msgID
WHILE @i <> 1
BEGIN
SELECT @one = @one + CAST(AttachmentID As Varchar(100)) + ' ' FROM t_Attachments
SELECT @two = @two + [FileName] + ' ' FROM t_Attachments
SELECT @three = @three + Cast(Cast(AttachmentContents as varbinary) as varchar(100)) + ' ' FROM t_Attachments
SET @i = @i - 1
END
SET @Result = Cast(@msgID as varchar(50)) +' '+@Result+@one+','+@two+','+@three
RETURN @Result
END
SELECT dbo.ReturnAll(19)
Cheers,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
CREATE FUNCTION ReturnAll (@msgID VARCHAR(8000) )
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result varchar(8000)
,@i int
,@one varchar(100)
,@two varchar(100)
,@three varchar(255)
SET @one = ''
SET @two = ''
SET @three = ''
SET @result = ''
SELECT @i = Count(*) FROM t_Attachments WHERE msgID = @msgID
WHILE @i <> 1
BEGIN
SELECT @one = @one + CAST(AttachmentID As Varchar(100)) + ' ' FROM t_Attachments
SELECT @two = @two + [FileName] + ' ' FROM t_Attachments
SELECT @three = @three + Cast(Cast(AttachmentContents as varbinary) as varchar(100)) + ' ' FROM t_Attachments
SET @i = @i - 1
END
SET @Result = Cast(@msgID as varchar(50)) +' '+@Result+@one+','+@two+','+@three
RETURN @Result
END
SELECT dbo.ReturnAll(19)
Cheers,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
If there are only 2 attachments then
SELECT
a1.AttachmentID AS [AttachmentID1],
a1.MsgID AS [MsgID1],
a1.[FileName] AS [FileName1],
a1.AttachmentContents AS [AttachmentContents1],
a2.AttachmentID AS [AttachmentID2],
a2.MsgID AS [MsgID2],
a2.[FileName] AS [FileName2],
a2.AttachmentContents AS [AttachmentContents2]
FROM t_Attachments a1
INNER JOIN t_Attachments a2
ON a2.MsgID = a1.MsgID
AND a2.AttachmentID = 2
WHERE a1.MsgID = 19
AND a1.AttachmentID = 1
Questions
1. Why
2. How will this be processed (how will you know which column is which, unless you use datatype)
3. Is there a limit for the number of attachments and are they all present for each MsgID?
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for all the responces.....
Let me try and answer your questions David.
1. Because they powers that be request the results in such a formate.
2. The application will know....
3. there is no limit to the number of attachments present.
However there has been a twist to the tale. The request has now changed for a much simpler answe.
they now want an extra column field for each rowreturned query which has a count of attachments for the record.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
Provided the AtachmentID's are sequential for each MsgID (and start at 1) then you could try this
DECLARE @maxid int,@count int,@sql nvarchar(1000),@countstr varchar(10)
SELECT @maxid = MAX([AttachmentID]) FROM t_Attachments
CREATE TABLE #temp ([MsgID] int,[AttachCount] int)
INSERT INTO #temp SELECT [MsgID],MAX([AttachmentID]) FROM t_Attachments GROUP BY [MsgID]
SET @count=0
WHILE @count<@maxid
BEGIN
SET @count=@count+1
SET @countstr=CAST(@count as varchar)
SET @sql = 'ALTER TABLE #temp ADD [AttachmentID'+@countstr+'] int,[FileName'+@countstr+'] varchar(100),[AttachmentContents'+@countstr+'] image'
EXEC(@sql)
END
SET @count=0
WHILE @count<@maxid
BEGIN
SET @count=@count+1
SET @countstr=CAST(@count as varchar)
SET @sql = '
UPDATE t SET t.[AttachmentID'+@countstr+']=a.AttachmentID,t.[FileName'+@countstr+']=a.FileName,t.[AttachmentContents'+@countstr+']=a.AttachmentContents
FROM #temp t
INNER JOIN t_Attachments a ON a.[MsgID] = t.[MsgID] AND + @countstr
EXEC(@sql)
END
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply