CODEWITHSUNDEEP
sqlsql-server-2012
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

Add a column with the same value to the results (not to table on db)

Is there an easy way to add a column with NewColumnName containing only one value across the rows (i.e NameIwantToAssign) to CTE? I do not want to change the table in database, only I want to have the new column in the results. Here is something I have cooked. But this is very bad idea with respect to performance. Please help.

SELECT 
     A.Something
    ,CASE 
    WHEN A.Something=NULL THEN 'NameIwantToAssign'
        ELSE 'NameIwantToAssign'
    END AS 'NewColumnName'
FROM TabA A

Update. Surprisingly, the method proposed by Raging Bull, which is very simple, is much slower than this code:

WITH B AS (SELECT 'NameIwantToAssign' as 'NewColumn')

SELECT * TabA A
LEFT JOIN B
ON A.Key <> B.NewColumn

BTW. I would like to get rid of <> from an ON statement for performance reasons. Is it possible to use something better then <>, something like IS NOT, NOT EXISTS etc?

Upvotes: 1

Views: 13071

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

To select a value along the result:

SELECT 'ValueIWantToAssign' as NameIwantToAssign,
       A.Something
FROM TabA

If you want to select a value if A.Something is null, you can use COALESCE in SQL.

SELECT COALESCE(A.Something,'ValueIwantToAssign')
FROM TabA

Explanation:

If the field A.Something is null, then it will select 'NameIwantToAssign'.

Read more about COALESCE here.

Upvotes: 1

Related Questions