CODEWITHSUNDEEP
sqlsql-server
Mahdi Bagheri
Mahdi Bagheri

Reputation: 23

if field equal by a value create new column in sql

I have this table:

persnid  |  elemntref |   val
   100   |     1      |   5000
   100   |     2      |   2500
   100   |     3      |   3100

I want use the following conditions:

IF elemntref = 1 THEN create new column that name is 'pay'
IF elemntref = 2 THEN create new column that name is 'pay2'
IF elemntref = 3 THEN create new column that name is 'pay3'

table after run query:

   persnid  |  Pay   |   Pay2  |    pay3    
     100    | 5000   |   2500  |    3100

Upvotes: 0

Views: 3549

Answers (3)

Ullas
Ullas

Reputation: 11556

You can also do this with dynamic sql query, if the number of rows are unknown.

Query

declare @sql as varchar(max);
select @sql = 'select [persnid], ' 
+ stuff((select distinct  ', coalesce(sum(case [elemntref] when ' +  
   cast([elemntref] as varchar(50)) +  
   ' then [val] end), 0) as [pay' + cast([elemntref] as varchar(10)) + ']'
   from [your_table_name]
for xml path('')
), 1, 2, '')
+' from [your_table_name] group by [persnid]';

exec(@sql);

Upvotes: 0

PowerStar
PowerStar

Reputation: 895

You want a CASE here

SELECT persnid
        ,SUM(CASE WHEN elemntref = 1 THEN ISNULL(val,0) END) AS pay
        ,SUM(CASE WHEN elemntref = 2 THEN ISNULL(val,0) END) AS pay2
        ,SUM(CASE WHEN elemntref = 3 THEN ISNULL(val,0) END) AS pay3
FROM YOUR_TABLE
GROUP BY persnid

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

If the elemntref column values fixed to 1,2,3 then the Normal Pivot works

Schema:

SELECT * INTO #TAB1 FROM (
SELECT 100 PERSNID, 1 ELEMNTREF, 5000 VAL
UNION ALL
SELECT 100, 2 , 2500
UNION ALL
SELECT 100, 3, 3100
)A

Now do pivoting with 1,2,3 as columns

SELECT PERSNID, [1] AS PAY, [2] AS PAY2, [3] AS PAY3 FROM (
SELECT * FROM #TAB1
)A
PIVOT
(
SUM(VAL) FOR ELEMNTREF IN ([1],[2],[3])
)PVT

The result will be

+---------+------+------+------+
| PERSNID | PAY  | PAY2 | PAY3 |
+---------+------+------+------+
|     100 | 5000 | 2500 | 3100 |
+---------+------+------+------+

Upvotes: 1

Related Questions