CODEWITHSUNDEEP
sql-server
rastus89
rastus89

Reputation: 13

Creating contra values in the same column SQL

Aim to write a statement to produce a table with a numeric column that contains contra values, e.g. 

Ref     Value
a       100
b       75
c       50
c       -50
b       -75
a       -100

I am new to SQL but aware it works row by row, so the only way I could think of doing this is write an initial SELECT statement into a temporary table and INSERT into my temporary table with the contra values, i.e. 

SELECT  
  [Ref],  
  [Value]  
  INTO #Temp  
FROM  
  mytable
INSERT INTO #Temp ([Ref], [Value])
SELECT
  [Ref],
  0 - [Value]
FROM
  mytable

While this 'does the job' I fear it is 'messy' (could possibly cause problems when used for its intended purpose) and wondered if anyone would be able to provide a better solution.

Upvotes: 1

Views: 295

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Union ALL to combine the original and negative values. Then insert into temp table. Try this.

SELECT [Ref],
       [Value]
INTO   #Temp
FROM   (SELECT [Ref],
               [Value]
        FROM   mytable
        UNION ALL
        SELECT [Ref],
               [Value] * -1
        FROM   mytable) a

If you just want to view the result remove the into #temp table

SELECT [Ref],
       [Value]
FROM   (SELECT [Ref],
               [Value]
        FROM   mytable
        UNION ALL
        SELECT [Ref],
               [Value] * -1
        FROM   mytable) a

Upvotes: 1

Related Questions