How do I append a column in selected rows?

Question

0.00/5 (No votes)

See more:

Not sure if I worded that correctly so here's an example of what I want to do.

STID	MARKS1 	MARKS2 	MARKS3	REMARK
01	    50		76		44		PROMOTED
02 	    80 		77		69		PROMOTED
03	    null	60		56		FAIL
04	    88		40		56		PROMOTED

This is the result I’m after

STID	MARKS   REMARK
01	    50		
01	    76
01	    44		PROMOTED
02 	    80
02	    77
02	    69		PROMOTED
03	    60
03	    56		FAIL
04	    88
04	    40
04	    56		PROMOTED

What I have tried:

I used this

SQL

SELECT STID, MARKS, REMARK
FROM
(
  SELECT STID, MARKS1, MARKS2, MARKS3, REMARK
  FROM dbo.student
) AS cp
UNPIVOT 
(
  MARKS FOR STUDENTMARKS IN (MARKS1, MARKS2, MARKS3)
  ) AS up

And got this as the result.

STID	MARKS   REMARK
01	    50		PROMOTED
01	    76		PROMOTED
01	    44		PROMOTED
02 	    80		PROMOTED
02	    77		PROMOTED
02	    69		PROMOTED
03	    60		FAIL
03	    56		FAIL
04	    88		PROMOTED
04	    40		PROMOTED
04	    56		PROMOTED

Posted 17-Jan-21 22:45pm

experimentalzee

Updated 17-Jan-21 23:48pm

Richard Deeming

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2021-01-17T23:48:00

Assuming you only want the remark to show on the last row, try:

SQL

SELECT
    STID,
    MARKS1 As MARKS,
    CASE WHEN MARKS2 Is Null And MARKS3 Is Null THEN REMARK END As REMARK
FROM
    dbo.student
WHERE
    MARKS1 Is Not Null

UNION ALL

SELECT
    STID,
    MARKS2 As MARKS,
    CASE WHEN MARKS3 Is Null THEN REMARK END As REMARK
FROM
    dbo.student
WHERE
    MARKS2 Is Not Null

UNION ALL

SELECT
    STID,
    MARKS3 As MARKS,
    REMARK
FROM
    dbo.student
WHERE
    MARKS3 Is Not Null
;

If you want to preserve the output order:

SQL

WITH cte As
(
    SELECT
        STID,
        1 As SEQ,
        MARKS1 As MARKS,
        CASE WHEN MARKS2 Is Null And MARKS3 Is Null THEN REMARK END As REMARK
    FROM
        dbo.student
    WHERE
        MARKS1 Is Not Null
    
    UNION ALL
    
    SELECT
        STID,
        2 As SEQ,
        MARKS2 As MARKS,
        CASE WHEN MARKS3 Is Null THEN REMARK END As REMARK
    FROM
        dbo.student
    WHERE
        MARKS2 Is Not Null
    
    UNION ALL
    
    SELECT
        STID,
        3 As SEQ,
        MARKS3 As MARKS,
        REMARK
    FROM
        dbo.student
    WHERE
        MARKS3 Is Not Null
)
SELECT
    STID,
    MARKS,
    REMARK
FROM
    cte
ORDER BY
    STID,
    SEQ
;