Combining multiple rows into one

I have data that looks like this:
ID Value
1 Descr1
1 Descr2
1 Descr3
where Descr could range from 1 to 100 for each ID
The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank youselect case when Descr1=... end as Descr1, case when Descr2=... end as Descr2, case when Descr3=... end as Descr3 from <your table name>

my first contribution here, quite similar to what I was trying to do in a project, do correct me if it's wrong.|||I will not be able to use CASE since the values of Descr1 etc. are always different|||What i mean is that the values in Value column are always different and are unknown. Therefore i will not be able to use CASE|||Ta da!

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx|||this is pretty much what adam is doing, but it's more compact and mysterious the first time you see it:

http://sqlblindman.googlepages.com/creatingcomma-delimitedstrings

plus it's from a regular here. :)|||Thank you.

Basically I came up with the following:

DROP FUNCTION dbo.ConcatDescr
go

CREATE FUNCTION dbo.ConcatDescr(@.TXRCODE CHAR(8))
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @.Output VARCHAR(300)
SET @.Output = ''
SELECT @.Output = CASE @.Output
WHEN '' THEN MON_TEXT
ELSE @.Output + ', ' + MON_TEXT
END
FROM PCLONG
WHERE TXRCODE = @.TXRCODE
order by MON_PCH

RETURN @.Output
END
GO

SELECT TXRCODE, dbo.ConcatDescr(TXRCODE)
FROM PCLONG
WHERE TXRCODE = '01100008'

The code above works to concatenate lines into one however it truncates data after 256 characters. I looked in help and it says that varchar can be up to 8000 chars. Is there something I am doing wrong?

Thank you again.|||it is because of the length of the variable where u r putting the data...increase it and the Return as well

RETURNS VARCHAR(300)
......
DECLARE @.Output VARCHAR(300)|||Your data may also be getting truncated by Query Analyzer. Check the QA options and bump up the maximum character output enough to display your results.

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 calculations

Hi,

I have a select query that returns three integer fields from a table the
values range from 0 to 5. On each row I would like to calculate the average
value in the three fields however, the difficulty is that only the rows
where the value is greater than 0 should be included in the calculation.

To make this clearer please consider the following example:

Col1 Col2 Col2 Average Calculation
1 2 3 2 (Col1+Col2+Col3)/3
2 2 0 2 (Col1+Col2+Col3)/2

The first row should be divided by 3 because each of the three columns has a
value greater than 0, however the second row should be divided by 2 because
only two of the value are greater than 0.

Is this possible?

Also is is possible to pass the results of one calculation into another
calculation.

Thanks in advance,

SteveSELECT col1, col2, col3, (col1 + col2 + col3)/(case when col1 = 0 then
0 else 1 end + case when col2 = 0 then 0 else 1 end + case when col3 =
0 then 0 else 1 end)
FROM My_Table

-Tom.|||(col1+col2+col3) /
(SIGN(col1)+ SIGN(col2)+ SIGN(col3))

If they can be negative, use ABS(SIGN (X))|||Thanks Tom,

This worked fine.

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1109973262.691311.81820@.o13g2000cwo.googlegro ups.com...
> SELECT col1, col2, col3, (col1 + col2 + col3)/(case when col1 = 0 then
> 0 else 1 end + case when col2 = 0 then 0 else 1 end + case when col3 =
> 0 then 0 else 1 end)
> FROM My_Table
> -Tom.