J.R. Brown J.R. Brown - 1 month ago 7
SQL Question

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

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.

Answer

Try this:

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