CODEWITHSUNDEEP
sqlvbaexcel
scraaappy
scraaappy

Reputation: 2886

Select columns referenced by name in another one to build a sql query

I have a table in excel. One of the column, named "target" is filled with reference to others columns names.
I want to sum values on some columns (for example column C) and sum values in the column referenced in column "target".
Is it possible to build a SQL query to do something like this?
for example :
`

select [column B] as colB, [target] as myTarget, sum([colC]) as sumC, sum([myTarget]) as sumTarget
where [column A]=myVar
group by [column B], [target]

`

I actually do this : 

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT  [column B] as colB, [target] as myTarget, "
strSQL = strSQL & "SUM([column C]) as sumC "
strSQL = strSQL & "FROM [BDD$" & myRange & "] "
strSQL = strSQL & "WHERE [column A] ='" & myVar & "' "
strSQL = strSQL & "group by [column B], [target]"
rs.Open strSQL, cn
rs.MoveFirst
Do While Not rs.EOF
    Set rs2 = CreateObject("ADODB.Recordset")
    strSQL2 = "SELECT SUM ([" & rs.Fields("myTarget") & "]) "
    strSQL2 = strSQL2 & "FROM [BDD$" & myRange & "] "
    strSQL2 = strSQL2 & "WHERE [column A] ='" & myVar & "' "
    rs2.Open strSQL2, cn
    Debug.Print "rs2 ---> " & rs2.Fields(0)
    rs2.Close
    Set rs2 = Nothing
    rs.MoveNext
Loop

I guess there is a simple way. Thanks for your help.

columnA   target       columnB       columnC    columnAA    columnAB
--------------------------------------------------------------------
test    columnAA          valA          100         10          1
other   columnAA          valB          200         20          2
test    columnAA          valA          300         30          3
test    columnAB          valB          400         40          4
test    columnAB          valA          500         50          5

if myVar=test, I want 

valA, target = columAA, 100+300,10+30  
valA, target = columAB, 500,5  
valB, target = columAB, 400,4

Upvotes: 1

Views: 875

Answers (1)

radar
radar

Reputation: 13425

you can use case based aggregation

select [columnB] as colB, 
       [target] as myTarget, 
       sum([columnC]) as sumC,
       sum( case when target ='columnAA' then columnAA 
                 when target ='columnAB' then columnAB 
            end)
       as sumMyTarget
from table1
where [columnA]= 'test'
group by [columnB], [target]

Upvotes: 2

Related Questions