CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
Biswajeet
Biswajeet

Reputation: 362

Select dynamic column based on rows in select query

Data structure is as follow

Table 1:

Col1a   Col1b   Col1c   Col1d
-----------------------------
 d11a    d11b    d11c    d11d
 d12a    d12b    d12c    d12d

Table 2:

Col2a   Col2b   Col2c   Col2d   Col2e
-----------------------------------
 s21a    s21b    s21c   Col1b   SUM(Col1b)
 s22a    s22b    s22c   Col1c   SUM(Col1c)
 s23a    s23b    s23c   Col1c   SUM(Col1c)
 s24a    s23b    s23c   Col1d   SUM(Col1d)

As you can see what I need to do. Presently 'Col2e' is empty now, I need to update it based on the cell data found in 'Col2d'.

Note: Both table doesn't have any identity column.

I tried using function, to replicate in select query but, it can't be used since it can not be used to select columns dynamically. I could not find a way to select/ update using Exec/ Exec sp_executesql.

Any suggestion will be appreciated.

Edit: I future following table might be used for mapping

Table ColumnMapping:

ColRowData  ColActualName   ColInTable
-----------------------------
 QTDCol  janQtd  Col1b
 FYCol   janFY   Col1c

This is why I wanted my column selections to be dynamic.

Upvotes: 0

Views: 81

Answers (2)

PSK
PSK

Reputation: 17953

If I understand your question, query like following should work for you.

update t2 
    set Col2e = (select case 
                        when t1.col1b = t2.col1b then Sum(col1b) 
                        when t1.col1c = t2.col1c then Sum(col1c) 
                        else Sum(col1d) 
                    end 
            from   table1 t1) 
from   table2 t2

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Is this what you need?

select t2.*,
       (case when t2.col2d = 'Col1a' then sumcol1a
             when t2.col2d = 'Col1b' then sumcol1b
             when t2.col2d = 'Col1c' then sumcol1c
             when t2.col2d = 'Col1d' then sumcol1d
        end) as col2e
from table2 t2 cross join
     (select sum(col1a) as sumcol1a, sum(col1b) as col1b,
             sum(col1c) as sumcol1c, sum(col1d) as col1d
      from table1 t1
     ) t1;

Upvotes: 3

Related Questions