Re: SQL Question: Combine Several Rows Into One Column?

Michael Naughton wrote:

Thanks to everyone who suggested pivot tables, but I'm afraid they won't help me with this. The problem is that the values to
be concatenated are notes -- basically, we have a notes table
with each note line being one row, and the user wants to see all the notes going horizontally across the page. So a better example
of what they want me to do is, given:

PO# Note
1 Ship the swimsuits
1 to facilty in Tampa
1 and the skis
1 to our Calgary warehouse
2 Confirmed by Irma
2 this will ship Thursday

the user wants to see this:

PO# Note
1 Ship the swimsuits to facilty in Tampa and the skis to our Calgary warehouse
2 Confirmed by Irma this will ship Thursday

Charles Wilt writes:

You might be able to write a UDF that would do it....

You can do a pivot... <<SNIP>>

Then maybe run the pivoted table through some field
concatenations...

But since the i doesn't have a built-in pivot function, you
have to have a predefined set of values in sub-set to pivot
on.

Since the concatenated row data has a required collation, what column would be used to order each concat operation? The data in the table is merely a set to the SQL, so the physical order of the rows is meaningless in a query. An actual column should define each /line number/ of the note data for each PO. Does such a column exist? Writing a UDF to concatenate the selected rows from the matching PO passed as a parameter has the same requirement, to enable proper collation of the concatenated note-column data.

The following is untested, but I think accomplishes effectively what was asked:

<code>

CREATE TABLE WhyNotVC (PO int, Line int, Note char(30))
;
insert into WhyNotVC values
( 1 , 1 , 'Ship the swimsuits ')
,( 1 , 2 , 'to facilty in Tampa ')
,( 1 , 3 , 'and the skis ')
,( 1 , 4 , 'to our Calgary warehouse ')
,( 2 , 1 , 'Confirmed by Irma ')
,( 2 , 2 , 'this will ship Thursday ')
;
with
Appended (PO, Line, Note) as
( select PO, Line, varchar(Type, 3000)
from WhyNotVC
where Line=1
/* first line of each PO */
union all
/* next line of each PO */
select a.PO, a.Line+1
,strip(a.Note) concat ' ' concat strip(w.Note)
/* perhaps rtrim() is better here. */
from Appended a
inner join WhyNotVC w
on a.PO=w.PO
where a.Line+1 = w.Line
)
,LastLine (PO, MaxLine) as
( select PO, Max(Line)
from WhyNotVC
)
select a.PO, a.Note
from Appended a
inner join LastLine l
on a.PO=l.PO
and a.Line=l.MaxLine

</code>

Regards, Chuck