September 7, 2005 at 9:01 am
Hi - I have a table which has information for one article, split across many rows:
ID ArtID ArtText
42 1 MS Outlook
43 1 Calendar
44 1 Adding appointments
45 2 MS Word
46 2 Headers and Footers
47 2 How to add a header or footer
I would like to create a temporary table, which has all of the article text in 1 record, ie:
ID ArtID ArtText
1 1 MS Outlook, Calendar, Adding Appointments
2 2 MS Word, Header and Footers, How to add a header or footer
I think the logic would be along the lines of:
Dim @id as integer = 0
Dim @tempstring as string
Create temp table
Add ArtID as integer
Add ArtText as TEXT
Select ArtID, ArtText from tblArticles
for Each row
If ArtID<>@id
temptablerow movenext
@id=ArtID
end if
temptablerow.ArtText = temptablerow.ArtText & ArtText
Next
Is there anyway I can do this from within a Stored Procedure, leaving the new temp table so I can then go on to query it separately??
Thanks for any pointers.
Mark
September 7, 2005 at 9:12 am
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply