Building SQL SELECT statement within EXCEL

Question

I am trying to build SQL statements within excel.

Sample:

I am trying to build sql statement, but wanted to only add the column when there is a value in the DIM columns. So the SQL looks neat without any syntax error when executed in SQL.

Here how can I eliminate the extra commas before the "from" keyword?

Used this to generate those statements.

="SELECT RET_ID,RET_NM, "&C2&","&D2&","&E2&","&F2&" FROMTABLEX"

shrivallabha.redij · Accepted Answer · 2020-05-14 04:10:04Z

3

Here's an approach which shall work for all versions of Excel

="SELECT RET_ID,RET_NM,"&SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",",")&" FROMTABLEX"

You can add any number of columns inside TRIM().

answered May 14, 2020 at 4:10

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

Note that should any of the column names have spaces in them (in which case they would have to be escaped), this answer might not generate the correct select statement.

shrivallabha.redij Over a year ago

@TimBiegeleisen valid observation!

Tim Biegeleisen · Accepted Answer · 2020-05-14 03:47:41Z

2

Starting with Office 2019, the TEXTJOIN function is very helpful here, because it lets you build a CSV string with optional components:

="SELECT RET_ID, RET_NM, "&TEXTJOIN(", ", TRUE, C2, D2, E2, F2)&" FROM TABLEX"

If you are using an earlier version of Excel, you can still pull off what you want, by using IF() combined with CONCATENATE(), but the logic is more verbose.

answered May 14, 2020 at 3:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

JvdV Over a year ago

Or a little shorter: ="SELECT RET_ID, RET_NM, "&TEXTJOIN(", ",1,C2:F2)&" FROM TABLEX"

Collectives™ on Stack Overflow

Building SQL SELECT statement within EXCEL

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related