RE: Possible?

From: Michael, Cheng [MSFT] (v-mingqc_at_online.microsoft.com)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 10:05:19 GMT

Hi Jordy,

Thank you for your posting.

>From your description, I understand that you would like to know combine
rows into a single field. For instance, you want the result like
Field1 Field2
===== ======
1 2,3,4,5
from the original table
Field1 Field2
===== ======
1 2
1 3
1 4
1 5
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

First and foremost, you'd better make all these like of operation bu your
front application by using cursor.

Secondly, you could not make a View to complete it, However, you may do
like this in Query Analyzer by means of exporting and importing:
use Northwind
go
create table t1 (item varchar(20) primary key)
go
-- Create a view to add the single quotes.
create view v1 (item) as
select item from t1
go
-- Make some test data in the format '10248-0011'.
insert t1
select cast(OrderID as varchar) + '-' +
       right('0000' + cast(ProductID as varchar), 4)
from Northwind..[Order Details]
order by OrderID

select count(*) from t1
--select * from t1
go
create procedure make_string as

set nocount on
declare @ptr binary(16)
declare @len int

create table #t2 (string text)

-- Export the table using comma as the row terminator.
-- Mind the usage of bcp command, if your Password is not sa :)
exec master..xp_cmdshell 'bcp Northwind..v1 out C:\v1.dat -c -r, -T -S. -P
sa'

-- Import the file as a single row.
-- The commas are now part of the data.
bulk insert #t2 from 'C:\v1.dat'

-- Remove extraneous trailing comma.
select @len = datalength(string) - 1,
        @ptr = textptr(string)
from #t2
updatetext #t2.string @ptr @len 1 null

-- Return the string as a result set.
select datalength(string) "length", string from #t2
drop table #t2
return 0
go
exec make_string
go
-- You could leave whatever you want, but remind to clear them
drop procedure make_string
drop view v1
drop table t1

Hope this helps, if you have any questions or concerns, don't hesitate to
let me know.

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Relevant Pages

  • RE: Question on the replace function for Inserting into SQL
    ... Do you mean the generated string added a comma or when you add a comma to ... To avoid dealing with the reserved characters in the SQL statement, ... "This posting is provided "AS IS" with no warranties, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: xp_smtp_sendmail attachment question
    ... This posting is provided "AS IS" with no warranties, ... >I cannot get the below to grab multiple attachments, ... > Declare @datecreated as varchar ... > Declare @string as varchar ...
    (microsoft.public.sqlserver.tools)
  • Re: CDBL Function truncates decimals
    ... Public Function ConvertNumberforSQL (ByVal sValue As String, ... vlDefaultNumber As Double= 0) as Double ... Since the Spanish formatting has a comma, the SQL statement seems to have ...
    (microsoft.public.vb.general.discussion)
  • Re: Finding multiple occurrences of character in word document
    ... the text into a string, and do a Split with the comma as a delimiter. ... For Each rngSentence In ActiveDocument.Sentences ... highlight the two positions. ...
    (microsoft.public.word.vba.general)
  • Re: MVP Needed - Parse a string of text from a linked .txt file
    ... delimiting the string on the comma. ... Dim rs As DAO.Recordset ... Dim strAddrPartsAs String ...
    (microsoft.public.access.modulesdaovba)