Step 1: generate some dummy data

/* create a table to hold the country and province values  */
/* as well as a column to store the cntry_prov_code...

If your database originally resided on a different server (with a different collation), columns created would have used that default. Columns created after the migration would default to the...

Why do you declare province to be char(2) and then assign it left(col_name, 1) which is only 1 character?

Does the sequence reset for each country?

What is the needed sort order...

Suppose the code you need to calculate is called the "Country Province Number" CPN.  The issue here is the example you provided only declares variables for a single record and...

You have 2 tables?

1. Dates  -- contains the master list of dates.  Table has 1 column.
2. Stations -- contains the date and # of stations.  Table has 2 columns

The output...

The "or" we were looking for.  The second example, the one with "coalesce", consolidates the 3 columns (Employed, Not employed, Retired) into 1 column (Employed status) by selecting the first...

The first post referenced:

The employment status depending on the Name would be "Manager", "Fired", "Enjoying Life". I basically need to insert 3 different columns into a single column.

Then you wrote:

How...

Well it's still not clear to me.  Here are examples of both.  If you're choosing concat then make sure table_b has wide enough column to fit (including the spaces).

            
August 15, 2019 at 10:34 pm
            
#3671711

            
            
                                                
        

Do you want concatenation or coalescence of the 3 columns?  Can be 1 and only 1 value per row in these 3 columns?  Could all 3 columns have values in...

You posted 2 code blocks.  It appears there is an issue with the second one.  Where you define the cte:

VESValidEnrollList AS(
  SELECT 
   ...

So it's turtles all the way.  At some point you must have a statement that access the cte's yes?  Because if you define a cte and don't use it sql...

It won't let me post code.  Please add a select statement that references the common table expression.

The spam filter will not let you post "select *star*" which is kind of annoying on a Sql site.

Sorry, the 1,2,3,... syntax only works in sort by in Sql Server.   Can't tell if the syntax parses because the tables aren't in my test instance.  Please try:

            
August 15, 2019 at 3:58 pm
            
#3671597