How do I dynamically create column in a SQL view using a Select statement?

Question

I currently have this statement in my SQL View (SQL Server 2008 R2) which works great for taking these two tables and displaying them in a single SQL View.

SELECT     sym1, msg1 
FROM       table_src1
UNION ALL
SELECT     sym2, msg2
FROM       table_src2

Expanding on this, I'm looking for a way to dynamically create/generate a column in this View. Let's call the column 'src'. This column should be a varchar data type and set to 'src1' or 'src2' based on the above select statement per row.

Example data the View would return would look like this:

sym        msg         src
symbolA    morning     src1
symbolB    night       src2

Can this be accomplished within my View? I've read similar questions where the answer is typically a stored procedure but nothing is saying it can't be done in a View.

Does this answer your question? automatically create views for a bunch of tables — Jim G.
– Jim G., Commented Jan 3, 2024 at 21:35

Andrey Gurinov · Accepted Answer · 2012-03-21 04:00:24Z

1

Try this:

SELECT     sym1, msg1, 'src1' as src 
FROM       table_src1 
UNION ALL 
SELECT     sym2, msg2, 'src2' as src 
FROM       table_src2

answered Mar 21, 2012 at 4:00

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

How do I dynamically create column in a SQL view using a Select statement?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related