return the column name from dynamic string

Question

I want to get the column from the dynamic string, In my table: MyTable2 I have many columns/fields, which are : MyVarCharColumnName1,MyVarCharColumnName2,MyVarCharColumnName3,MyVarCharColumnName4 These above fields are nvarchar(50), I wonder how I can get the value of them when join with another table as below example:

SELECT
  *,
  CASE
  WHEN Tbl1.MyId IN ('1', '2', '3', '4')
    THEN (SELECT 'MyVarCharColumnName' + bf.MyId
          FROM MyTable2)
  ELSE Tbl1.MyId
  END AS TheNameofId
FROM
  Tbl1

above is wrong because it will return MyVarCharColumnName1 as TheNameofId value instead of the value inside MyVarCharColumnName1

Is there are some sql function that can return the column name from a string? any way to convert 'MyVarCharColumnName1' to column object?

You can use dynamic sql.

Zohar Peled
– Zohar Peled

2018-05-22 09:25:17 +00:00
Commented May 22, 2018 at 9:25 — Zohar Peled
– Zohar Peled, Commented May 22, 2018 at 9:25

Shushil Bohara · Accepted Answer · 2018-05-22 09:53:22Z

1

As it is confirmed that you have to write dynamic query to do in the same way as you are asking but beside that we can do the same in the following way using CASE

SELECT
  *,
  CASE
      WHEN Tbl1.MyId = 1 THEN MyVarCharColumnName1
      WHEN Tbl1.MyId = 2 THEN MyVarCharColumnName2
      WHEN Tbl1.MyId = 3 THEN MyVarCharColumnName3
      WHEN Tbl1.MyId = 4 THEN MyVarCharColumnName4
      ELSE Tbl1.MyId
  END AS TheNameofId
FROM Tbl1

answered May 22, 2018 at 9:53

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

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

4 Comments

Zohar Peled Over a year ago

Yeah, this is a better approach than dynamic SQL. +1.

Bashar Abu Shamaa Over a year ago

I know this way, but the fields are 10 fields, and I don't want make 10 lines, However, I now the table designed not correctly, but that what is exist, and the above sample I provided is simplify the issue, Is there are any other creative idea?

Shushil Bohara Over a year ago

Would you like to go with dynamic solution ??

Bashar Abu Shamaa Over a year ago

@Susang No, I don't like dynamic sql, But there are many ms sql functions like COL_NAME or OBJECT_ID; but I can not use them to help

Collectives™ on Stack Overflow

return the column name from dynamic string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related