SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 combine records of a column of the same table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abidmisbah
Starting Member

India
5 Posts

Posted - 01/23/2005 :  04:56:35  Show Profile  Send abidmisbah a Yahoo! Message  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 01/23/2005 :  10:18:59  Show Profile  Visit nr's Homepage  Reply with Quote
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

India
5 Posts

Posted - 01/24/2005 :  12:59:49  Show Profile  Send abidmisbah a Yahoo! Message  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 01/24/2005 :  13:29:59  Show Profile  Visit nr's Homepage  Reply with Quote
>> 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

India
5 Posts

Posted - 01/24/2005 :  13:45:57  Show Profile  Send abidmisbah a Yahoo! Message  Reply with Quote
>>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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000