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 2000 Forums
 SQL Server Development (2000)
 combine records of a column of the same table

Author  Topic 

abidmisbah
Starting Member

5 Posts

Posted - 2005-01-23 : 04:56:35
hi, i have all my portal contents on the SQL Database in the following manner:
Node_id Contents
12 this the first page
12 welcome to the portal
12 this is some sample data
13 this the second page,
13 sample data

when a page is being loaded, depending on the node_id contents are retrieved and displayed. Now i need a query which would combine all records of the same Node_id (page) and display as one record.

Eg:
Node_id Contents
12 this the first page welcome to the portal this is...
13 this is the second page, sample data


anyone with a solution, please post a reply. thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-23 : 10:18:59
declare @data varchar(8000)
select @data = coalesce(@data+' ','') + Contents
from tbl
where Node_id = @node_id
select @data

Note that with that structure there is no way of specifying the order of the contents.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

abidmisbah
Starting Member

5 Posts

Posted - 2005-01-24 : 12:59:49
Hi NR, Thanks for the reply.>
i think you have not understood my question entirely

My actual situation is as below:
> Node_Id Contents
> 12 This is the first page
> 12 some sample content
> 12 extra line
> 12 not necessary record
> 13 Extra page
> 13 second page
> 13 this page talks about sql
> 13 second unnecessary record
>
> Depending on the Search Query, given by the user records will be retrieved in the following form.
i.e. Any records containing of ANY of the search words and joined together to form one single record.
>
> Eg1: Search String - "page" "extra" "talks"
> Node_Id Contents
> 12 this is the first page extra line
> 13 Extra page second page this page talks about sql
>
> Now, on the above contents a FTI Query will be run, which will check for the occurrence all the Search Words. After this records will consists of all the entries will be saved only.
>
> Node_Id Contents
> 13 Extra page second page this page talks about sql
>
> This is the only record saved, since it consists of all the search words. Also Note in my final output i also get the Node Id
>
> Hope this is clear enough and you come up with some solution. i have spent long hours on this, but in vain.

Not forgetting to mention, i am tryin to achieve this on ASP .net, hence will not be able to use any Local Variables. Anyway the local variable idea, doesnt fit here, since the local variable can hold only limited content, whereas in my case, i need something like an Global Variable which can hold unlimited content. Thank you once again
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-24 : 13:29:59
>> i need something like an Global Variable which can hold unlimited content
For this I guess you will have to create a temp table with a text datatpe then loop round concatenating all the row data together to form the records.
see
http://www.mindsdoor.net/SQLTsql/InsertTextData.html
for the sort of thing.

Another option would be to ge the id's of each row (what is the pk on this table) then create a dynamic sql statement to concatenate the data in a select statement.

I suspect you would be better just using a statement to return the rows needed and do the concatenation in the client.



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

abidmisbah
Starting Member

5 Posts

Posted - 2005-01-24 : 13:45:57
>>Another option would be to ge the id's of each row (what is the pk on this table) then create a dynamic sql statement to concatenate the data in a select statement.

How would i achieve this?
Go to Top of Page
   

- Advertisement -