Concatenate columns into a string

I am trying to concatenate 3 columns into 1 string but I need to sort
them alphabetically before I concatenate them.
Example
column1 = ABC
column2 = ABF
column3 = ABE
I need to insert the concatenate value into a column as "ABC ABE ABF"
I know how to concatenate the values but I don't know how the sort them
first.
I would appreciate any advice you can provide.
Thanks,You could try something like this as a brute-force approach:
create table #temp(column1 char(3), column2 char(3), column3 char(3))
insert #temp values('ABC', 'ABF', 'ABE')
insert #temp values('DEF', 'AAF', 'AAF')
select first + ' ' + middle + ' ' + last
from (select
case
when column1 <= column2 and column1 <= column3 then column1
when column2 <= column1 and column2 <= column3 then column2
when column3 <= column1 and column3 <= column2 then column3
end first,
case
when column1 >= column2 and column1 <= column3 then column1
when column1 >= column3 and column1 <= column2 then column1
when column2 >= column1 and column2 <= column3 then column2
when column2 >= column3 and column2 <= column1 then column2
when column3 >= column1 and column3 <= column2 then column3
when column3 >= column2 and column3 <= column1 then column3
end middle,
case
when column1 >= column2 and column1 >= column3 then column1
when column2 >= column1 and column2 >= column3 then column2
when column3 >= column1 and column3 >= column2 then column3
end last
from #temp) x
go
drop table #temp
go|||Have you ever worked in billing at a law firm?
:)
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1147465998.777145.245230@.v46g2000cwv.googlegroups.com...
> You could try something like this as a brute-force approach:
> create table #temp(column1 char(3), column2 char(3), column3 char(3))
> insert #temp values('ABC', 'ABF', 'ABE')
> insert #temp values('DEF', 'AAF', 'AAF')
> select first + ' ' + middle + ' ' + last
> from (select
> case
> when column1 <= column2 and column1 <= column3 then column1
> when column2 <= column1 and column2 <= column3 then column2
> when column3 <= column1 and column3 <= column2 then column3
> end first,
> case
> when column1 >= column2 and column1 <= column3 then column1
> when column1 >= column3 and column1 <= column2 then column1
> when column2 >= column1 and column2 <= column3 then column2
> when column2 >= column3 and column2 <= column1 then column2
> when column3 >= column1 and column3 <= column2 then column3
> when column3 >= column2 and column3 <= column1 then column3
> end middle,
> case
> when column1 >= column2 and column1 >= column3 then column1
> when column2 >= column1 and column2 >= column3 then column2
> when column3 >= column1 and column3 >= column2 then column3
> end last
> from #temp) x
> go
> drop table #temp
> go
>|||>> I know how to concatenate the values but I don't know how the sort them
If ordered display of these is significant to your business, perhaps you
should not be representing these values in a single row to begin with.
Please elaborate on your requirements, including some actual sample data,
and someone here can give some advice.
Anith|||(jdornan@.wideopenwest.com) writes:
> I am trying to concatenate 3 columns into 1 string but I need to sort
> them alphabetically before I concatenate them.
> Example
> column1 = ABC
> column2 = ABF
> column3 = ABE
> I need to insert the concatenate value into a column as "ABC ABE ABF"
> I know how to concatenate the values but I don't know how the sort them
> first.
I don't know you intend to concatenate them, but if you are on SQL 2000
you should run a cursor over the data, and the cursor definition should
include an ORDER BY clause. There are tricks that does not use a cursor,
but problem with these tricks is that they rely on undefined behaviour,
and may not yield the desired result.
On SQL 2005, there is another trick - but this time one that relies
on defined behaviour by using FOR XML PATH and XQuery. I show it here
with a sample from the Northwind database:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
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|||Thank you all for your input. Sorry for the vague post, I will
elaborate some more on what exactly I am doing.
I am running SQL Server 2000.
I currently get data from 4 different sources that I need to combine.
Each source has different parts of the information. For example:
Serial number 111222 for a widget.
Each widget has many different options that are listed as a 3 character
strings. AAA, BBB, CCC
The problem is that I get the options from 4 different sources so I
must combine all the options into 1 long string (separated by a space)
to be used by another application. I can join the tables with the
serial number as the primary key but the kicker is that the options
must be placed in 1 string in alphabetic order for the front end
application to function correctly.
I am using a DTS package to import and concatenate the data right now.
The data is dumped from a mainframe into 4 different txt files
(currently this is the only way we can obtain this data). The order in
which I import the 4 data sources works most of the time.
Unfortunately there are instances where we have option codes that are
not in alphabetic order and the front end app errors out.
I know we can change the front end application to do the sorting but
with 500,000 plus records at a time it would add quite a bit of
overhead to the app. My thought was to combine, sort, and store them
in one field on the server so the front end app doesn't have to do
that processing.|||(jdornan@.wideopenwest.com) writes:
> I am running SQL Server 2000.
> I currently get data from 4 different sources that I need to combine.
> Each source has different parts of the information. For example:
> Serial number 111222 for a widget.
> Each widget has many different options that are listed as a 3 character
> strings. AAA, BBB, CCC
> The problem is that I get the options from 4 different sources so I
> must combine all the options into 1 long string (separated by a space)
> to be used by another application. I can join the tables with the
> serial number as the primary key but the kicker is that the options
> must be placed in 1 string in alphabetic order for the front end
> application to function correctly.
> I am using a DTS package to import and concatenate the data right now.
> The data is dumped from a mainframe into 4 different txt files
> (currently this is the only way we can obtain this data). The order in
> which I import the 4 data sources works most of the time.
> Unfortunately there are instances where we have option codes that are
> not in alphabetic order and the front end app errors out.
> I know we can change the front end application to do the sorting but
> with 500,000 plus records at a time it would add quite a bit of
> overhead to the app. My thought was to combine, sort, and store them
> in one field on the server so the front end app doesn't have to do
> that processing.
That does not sound too fun, at least not on SQL 2000. You could
get the options into a SQL Server table, but you would then have
to run a cursor over the table to build the string for each widget.
I hope that the options are not stored in sources as lists as well,
because that would make things even worse.
Which will perform the worst, doing this in the server or in the
application, I don't know.
If you post CREATE TABLE statements for the tables, and INSERT statements
with sample data, it is possible that someone is able to find a shortcut
of some sort.
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