PDA

View Full Version : How do you do this with SQL code?



Magnus Bergh
6-Sep-2005, 09:54 AM
I need to create an SQL statement which do this:

Create a string (which should actually be put into an insert statement)
from two columns and it should look like this (col1 and col2 from the
table I am querying, result string is what I want it to look like):


Col1 Col2 Result string
123 NULL 123
123 1 123-1
123 2 123-2

Col1 is varchar and col2 integer

This is for MS SQL server (but if it is ANSI92 compliant then better).

Evertjan Dondergoor
6-Sep-2005, 10:04 AM
SELECT col1, col2, CASE WHEN col2 = NULL THEN col1 ELSE (col1 + '-' +
col2) END AS Resultstring
FROM mytable


"Magnus Bergh" <magnusb@sbbs.se> wrote in message
news:MPG.1d87cbf6567c44c8989eaf@news.dataaccess.co m...
>I need to create an SQL statement which do this:
>
> Create a string (which should actually be put into an insert statement)
> from two columns and it should look like this (col1 and col2 from the
> table I am querying, result string is what I want it to look like):
>
>
> Col1 Col2 Result string
> 123 NULL 123
> 123 1 123-1
> 123 2 123-2
>
> Col1 is varchar and col2 integer
>
> This is for MS SQL server (but if it is ANSI92 compliant then better).
>

Evertjan Dondergoor
6-Sep-2005, 10:15 AM
Of course I notice just one second too late col2 is an int. That you need to
convert. It will be like:

SELECT col1, col2, CASE WHEN col2 = NULL THEN col1 ELSE (col1 + '-' +
CONVERT(char(50), col2)) END AS Resultstring
FROM mytable

Evertjan


"Evertjan Dondergoor" <evertjan.dondergoor@dataaccess.nl> wrote in message
news:ftLYDRvsFHA.4044@dacmail.dataaccess.com...
> SELECT col1, col2, CASE WHEN col2 = NULL THEN col1 ELSE (col1 + '-' +
> col2) END AS Resultstring
> FROM mytable
>
>
> "Magnus Bergh" <magnusb@sbbs.se> wrote in message
> news:MPG.1d87cbf6567c44c8989eaf@news.dataaccess.co m...
>>I need to create an SQL statement which do this:
>>
>> Create a string (which should actually be put into an insert statement)
>> from two columns and it should look like this (col1 and col2 from the
>> table I am querying, result string is what I want it to look like):
>>
>>
>> Col1 Col2 Result string
>> 123 NULL 123
>> 123 1 123-1
>> 123 2 123-2
>>
>> Col1 is varchar and col2 integer
>>
>> This is for MS SQL server (but if it is ANSI92 compliant then better).
>>
>
>

Magnus Bergh
7-Sep-2005, 07:47 AM
In article <QkltkXvsFHA.1276@dacmail.dataaccess.com>,
evertjan.dondergoor@dataaccess.nl says...
> Of course I notice just one second too late col2 is an int. That you need to
> convert. It will be like:
>
> SELECT col1, col2, CASE WHEN col2 = NULL THEN col1 ELSE (col1 + '-' +
> CONVERT(char(50), col2)) END AS Resultstring
> FROM mytable

Thank you!

I had to change col2= null to Col2 is NULL instead and then it worked.