SQL Server dynamically adding column names in the select query

Question

Below is my query

SELECT 
    P.PlanName
    V.Section
FROM 
    app.Plan P
CROSS APPLY 
    (VALUES ('Protective'), ('Effective'), ('EasyClaim')) V(Section);

in the query, this returns the value in V.Section, where as I want to get the value of that column from the table variable.

You don't need to VALUES table construct for this; just JOIN your table app.Plan to the table variable *that you don't show in your question) on Plan. — Thom A
– Thom A ♦, Commented Dec 2, 2020 at 16:48
I can join the query to my table variable, but in my select query i still need to mention the name of the column of my table variable whose data i need to fetch... That name of the column will be V.Section AS [Section] — Sayan
– Sayan, Commented Dec 2, 2020 at 16:59
Thee name of column will be in the table varuiable, @Sayan, it'll be columns names. You can't use row data to define the name of a column without dynamic SQL, and I doubt that's required. Your table variable should have 3 columns already, with the names Protective, Effective, and EasyClaim; there's no need for the VALUES table construct. — Thom A
– Thom A ♦, Commented Dec 2, 2020 at 17:00
Your subquery that selects from your table variable is explicitly told to use V's value. SELECT V.Section FROM @TabVariable WHERE Name = P.[PlanName]. Assuming your table variable has a column named "Section", remove the V. from your Rating subquery. You will need to alias @TabVariable and reference that if the column names are the same. — critical_error
– critical_error, Commented Dec 2, 2020 at 17:04

Thom A · Accepted Answer · 2020-12-02 17:12:44Z

1

Like I said in the comments, you don't need the VALUES table construct; just JOIN to your table variable:

SELECT P.Plan,
       TV.Protective,
       TV.Effective,
       TV.EasyClaim
FROM app.Plan P
     LEFT JOIN @TableVariable TV ON P.Plan = TV.Plan; --I assume LEFT JOIN as otherwise you just want
                                                      --SELECT * FROM @TableVariable;

If you want the values for be 0 where the JOIN fails, then you use use ISNULL or COALESCE. For example: ISNULL(TV.Protective,0) AS Protective

edited Dec 2, 2020 at 17:12

answered Dec 2, 2020 at 17:04

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

2 Comments

Sayan Over a year ago

I used this query, this just returns 6 rows with my plan names and the sections and Protective, Effective, and EasyClaim as columns but where as i need Protective, Effective, and EasyClaim as rows beside each plan

Thom A Over a year ago

I think it's time for sample data and expect results, @Sayan; you're not makign any sense here.

Collectives™ on Stack Overflow

SQL Server dynamically adding column names in the select query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related