Combining many records into 1

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.

example tables:
CREATE TABLE [NoteHeader] (
[NoteID] [int],
[CustomerID] [int] ,
[Desc1] [varchar] (255),
[Date] [datetime] ,
)
GO

CREATE TABLE [NoteDetail] (
[NoteId] [int],
[SeqNum] [int] NOT NULL ,
[Note1] [varchar] (255),
[Note2] [varchar] (255),
[Note3] [varchar] (255),
[Note4] [varchar] (255),
[Note5] [varchar] (255)
)
GO

Sample script joining tables:
SELECT *
FROM NoteHeader INNER JOIN
NoteDetail ON NoteHeader.NoteID = NoteDetail.NoteId

Sample results:
NoteID CustomerID Desc1 Date
Note1 Note2
....Note5
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 Notes detail record 1 field2 ....
1111 987 Note Header Description 2007-07-15
Notes detail record 2 field 1 Notes detail record 2 field 2

Desired results:
NoteID CustomerID Desc1 Date
CombinedNotes
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 +

Notes detail record 1 field2 +

Notes detail record 2 field 1 +

Notes detail record 2 field 2 +

through unlimited number of records up to 5
fields each

The NoteID field is the unique number. 1 record per NoteID in NoteHeader,
NoteDetail can have unlimited number of same NoteID (usually not more than
10)rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need
help with the select statement.

SQL Server MVP Anith Sen as a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Thanks for the info. My problem is the resulting data will be too large
for varchar(8000). All these examples seem to use varchar(8000)
I need to convert to a text datatype. I can concat multiple varchar fields
from 1 record into text but the problem is how the source data is
structured.
The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

I think you have two options:

1) Get SQL 2005.
2) Do it client-side.

I think you can do it on SQL 2000, but then you would have to run
a cursor, and use WRITETEXT and UPDATETEXT and it would be very very
painful. Please don't ask me to write the code for you, but if you
have problems with using WRITETEXT and UPDATETEXT, I can try to assist.

Quote:

Originally Posted by

I assume it was designed this way because SQL 6.5 largest data type was
varchar(255) ?

Yes, that is correct.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Combine raw files using range

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

Sure, you can merge two raw files. You can merge any two data flow streams. The data just has to be sorted first.

You could load a staging table for each raw file, though, and write a SQL join against them to get your results as well...|||

ccote wrote:

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

This appears to be a problem that requires SQL, meaning that both data sets need to be stored in tables. As suggested previously, the data sets can then be joined to yield the desired result set.

I hope this helps.

|||

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

|||

ccote wrote:

I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

New feature requests may be submitted here: SQL Server Feedback|||Can you explain what you mean to accomplish by "merge them upon a range condition"?|||

ccote wrote:

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

Yes. The lack of non-equi-join functionality really annoys me. I once enquired why it was not there and was told "nobody has asked for it". Well...it seems people are now asking for it.

A connect submission for this already exists: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126376 so please please please click-through, vote and ADD A COMMENT (just voting is next to useless - they need to see real reasons why this needs to be implemented). You can link back to this thread as well.

-Jamie

|||

HI, I would like to be able to do basically the same thing as I can do when I join tables: Be able to join on col1 <= col2. One way to achieve this would be being able to to <= (or <>, <, >, etc.) when I use merge join transform. Or,by being able to lookup against raw files, currently, we can only use lookups transforms against reelational tables. If we could be able to do lookups against raw files, it would be perferct since lookups can have parameters and we can specify the query by using ranges.

Ccote

Combine raw files using range

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

Sure, you can merge two raw files. You can merge any two data flow streams. The data just has to be sorted first.

You could load a staging table for each raw file, though, and write a SQL join against them to get your results as well...|||

ccote wrote:

HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition:

RawFile1.Date <= RawFile2.Date

Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join?

Thank you,

Ccote

This appears to be a problem that requires SQL, meaning that both data sets need to be stored in tables. As suggested previously, the data sets can then be joined to yield the desired result set.

I hope this helps.

|||

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

|||

ccote wrote:

I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

New feature requests may be submitted here: SQL Server Feedback|||Can you explain what you mean to accomplish by "merge them upon a range condition"?|||

ccote wrote:

Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all the work locally and at the end send the result to SQL server target table in one pass. I guess merge join are not flexible enough for now, maybe MS will had non equijoin fucntionnality in future version.

Thank you,
Ccote

Yes. The lack of non-equi-join functionality really annoys me. I once enquired why it was not there and was told "nobody has asked for it". Well...it seems people are now asking for it.

A connect submission for this already exists: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126376 so please please please click-through, vote and ADD A COMMENT (just voting is next to useless - they need to see real reasons why this needs to be implemented). You can link back to this thread as well.

-Jamie

|||

HI, I would like to be able to do basically the same thing as I can do when I join tables: Be able to join on col1 <= col2. One way to achieve this would be being able to to <= (or <>, <, >, etc.) when I use merge join transform. Or,by being able to lookup against raw files, currently, we can only use lookups transforms against reelational tables. If we could be able to do lookups against raw files, it would be perferct since lookups can have parameters and we can specify the query by using ranges.

Ccote

Column mapping in SSIS

Hi,

My Destination columns are more than source columns....

So, how to do column mapping if my source and destination columns are different?

Thanx,

Ruja

Even if the number of columns is different, the mapping should be done automatically if the names of the destination columns are the same of the source.

For all the others destination columns ... you have to do it manually.

Cosimo

|||

Ruja wrote:

Hi,

My Destination columns are more than source columns....

So, how to do column mapping if my source and destination columns are different?

Thanx,

Ruja

I don't understand very well. Are you taking about diffrences in data types? for that you have to cast them to the proper data type using data Conversion or derived column transforms.

If the diffrence is just on the name; you have to create the mapping manually (drag and drop to create the arrows)...

If you have more columns on source than in the target....you shoud know what to do, since you are the only one that knows your data

|||If you simply have more columns in the destination than the source, you can "ignore" the destination columns that don't map to the source, and they'll simply get NULLs (or the default value if defined in the database schema) inserted into those fields that you ignore.

You don't have to match on the number of columns.|||

Ruja wrote:

Hi,

My Destination columns are more than source columns....

So, how to do column mapping if my source and destination columns are different?

Thanx,

Ruja

How can anyone except yourself possibly answer that question?

If you have more columns in your destination than in your source and this is a problem to you - change either your source or your destination.

-Jamie

Column Formulas

Looking for a decent source that has some examples of column formulas as
well as a list of column formula functions that can be used and how to use
them.

Hard time finding something online. Any suggestions or references would be
greatly appreciated.

Fairly new to this.

ThanksHi

Not sure exactly what you mean by column formulas! You may want to look up
computed columns in Books online.

John

"Scott Castillo" <scasti2@.cox.net> wrote in message
news:fLLZb.7255$C21.4494@.fed1read07...
> Looking for a decent source that has some examples of column formulas as
> well as a list of column formula functions that can be used and how to use
> them.
> Hard time finding something online. Any suggestions or references would
be
> greatly appreciated.
> Fairly new to this.
> Thanks