SQL Server 2014 : using value from columns to generate a new value for new column

Question

I have data like this:

account   period01      period02      period03     period04
-----------------------------------------------------------
1111      null          null          null         null
1112      782           null          null         null
1113      null          null          null         345
1114      765           882           67           321

What I want to do is to get values from period1 through period04 and use the last value to code the value of accoutperiod.

So if value is from period1, then code it as 01, period2 as 02, period03 as 03 and period04 as 04.

In the end, the output should be like this

account   period01      period02      period03     period04  accoutperiod
--------------------------------------------------------------------------
1111      null          null          null         null      null
1112      782           null          null         null      01
1113      null          null          null         345       04
1114      765           882           67           321       04  

Answer 1

Even though @ughai answer's will help you get what you need but it fails to explain you how does 'CASE' statement works in sql.

Hope my sample will guide you the way how does the case works with multiple 'WHEN' statements.

Let us consider your table.

;WITH cte (account, period01, period02, period03, period04)
AS
(
    SELECT
        1111, NULL, NULL, NULL, NULL
UNION ALL
    SELECT
        1112, 782, NULL, NULL, NULL
UNION ALL
    SELECT
        1113, NULL, NULL, NULL, 345
UNION ALL
    SELECT
        1114, 765, 882, 67, 321
)

Below query is the common mistake everyone does to achieve our result.

SELECT 
    account,
    period01,
    period02,
    period03,
    period04,
    CASE 
        WHEN period01 IS NOT NULL THEN '01'
        WHEN period02 IS NOT NULL THEN '02'
        WHEN period03 IS NOT NULL THEN '03'
        WHEN period04 IS NOT NULL THEN '04'
        ELSE NULL END AS [accoutperiod]
FROM 
    cte

The above query will Give you the following result:

╔═════════╦══════════╦══════════╦══════════╦══════════╦══════════════╗
║ account ║ period01 ║ period02 ║ period03 ║ period04 ║ accoutperiod ║
╠═════════╬══════════╬══════════╬══════════╬══════════╬══════════════╣
║    1111 ║ NULL     ║ NULL     ║ NULL     ║ NULL     ║ NULL         ║
║    1112 ║ 782      ║ NULL     ║ NULL     ║ NULL     ║ 01           ║
║    1113 ║ NULL     ║ NULL     ║ NULL     ║ 345      ║ 04           ║
║    1114 ║ 765      ║ 882      ║ 67       ║ 321      ║ 01           ║
╚═════════╩══════════╩══════════╩══════════╩══════════╩══════════════╝

Since first statement in the case has WHEN period01 IS NOT NULL THEN '01' , So the statement get's success in the first attempt and executed.

Now Let us Re-order the WHEN statement in reverse order.

SELECT
    account,
    period01,
    period02,
    period03,
    period04,
    CASE
        WHEN period04 IS NOT NULL THEN '04'
        WHEN period03 IS NOT NULL THEN '03'
        WHEN period02 IS NOT NULL THEN '02'
        WHEN period01 IS NOT NULL THEN '01'
        ELSE NULL END AS [accoutperiod]
FROM
    cte

The above query will Give you the following result:

╔═════════╦══════════╦══════════╦══════════╦══════════╦══════════════╗
║ account ║ period01 ║ period02 ║ period03 ║ period04 ║ accoutperiod ║
╠═════════╬══════════╬══════════╬══════════╬══════════╬══════════════╣
║    1111 ║ NULL     ║ NULL     ║ NULL     ║ NULL     ║ NULL         ║
║    1112 ║ 782      ║ NULL     ║ NULL     ║ NULL     ║ 01           ║
║    1113 ║ NULL     ║ NULL     ║ NULL     ║ 345      ║ 04           ║
║    1114 ║ 765      ║ 882      ║ 67       ║ 321      ║ 04           ║
╚═════════╩══════════╩══════════╩══════════╩══════════╩══════════════╝

Answer 2

You can use CASE like this.

SELECT account, period01, period02, period03, period04,
    CASE 
    WHEN period04 IS NOT NULL THEN '04'
    WHEN period03 IS NOT NULL THEN '03'
    WHEN period02 IS NOT NULL THEN '02'
    WHEN period01 IS NOT NULL THEN '01'
    ELSE NULL END as accoutperiod
From YourTable