How to separate field partdone to text yes or no or null based on position ?

Question

0.00/5 (No votes)

See more:

I work on SQL server 2012 and have table parts have two field

SQL

partId int
PartDone nvarchar(50)

SQL

partId    partDone
555444    0012
911877    0221

the length of partDone field is 4 numbers
I need to loop within partDone field

if 0 then no

if 1 then yes

if 2 then null

SQL

FinalResult
partId    partDone first  second third  four 
555444    0012     no      no     yes   null       
911877    0221     no      null   null  yes

this field partDone accept only 0 or 1 or 2

and I need to divide them to yes or no or null based on position of every number

What I have tried:

SQL

select partId,partDone from Parts

Posted 10-Mar-20 21:22pm

ahmed_sa

Updated 11-Mar-20 1:26am

v2

Add a Solution

Comments

Maciej Los 11-Mar-20 16:08pm

Does mine solution is wrong?

2 solutions

Solution 1

You can use CTE[^] togethe with PIVOT[^] for such of requirement. See:

SQL

DECLARE @tmp TABLE(partId int, PartDone nvarchar(50))

INSERT INTO @tmp(partId, partDone)
VALUES(555444, '0012'),
(911877, '0221');

;WITH CTE AS
(
	SELECT partId, PartDone, 1 AS LoopNo, LEFT(PartDone, 1) AS pdid, RIGHT(partDone, LEN(PartDone)-1) AS Remainder
	FROM @tmp
	WHERE PartDone IS NOT NULL
	UNION ALL
	SELECT partId, PartDone, LoopNo+1 AS LoopNo, LEFT(Remainder, 1) AS pdid, RIGHT(Remainder, LEN(Remainder)-1) AS Remainder
	FROM CTE 
	WHERE LEN(Remainder)>0
)
SELECT partId, PartDone, [1], [2], [3], [4]
FROM (
	SELECT partId, PartDone, LoopNo, CASE 
		WHEN pdid = 0 THEN 'no' 
		WHEN pdid = 1 THEN 'yes' 
		ELSE null END AS pdid 
	FROM CTE 
) AS dt
PIVOT(MAX(pdid) FOR LoopNo IN ([1], [2], [3], [4])) AS pt

Result:

partId	PartDone	1	2		3		4
555444	0012		no	no		yes		NULL
911877	0221		no	NULL	NULL	yes

Posted 10-Mar-20 21:47pm

Maciej Los

Updated 10-Mar-20 21:48pm

v2

Comments

phil.o 11-Mar-20 4:12am

5'd

Maciej Los 11-Mar-20 4:23am

Thank you very much.

MadMyche 11-Mar-20 6:48am

+5

Maciej Los 11-Mar-20 6:54am

Thank you very much.

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 · Accepted Answer · 2020-03-11T01:27:00

If the PartDone is always four numbers, then you don't need a PIVOT:

SQL

DECLARE @tmp TABLE (partId int, PartDone char(4));

INSERT INTO @tmp (partId, partDone)
VALUES
    (555444, '0012'),
    (911877, '0221')
;

SELECT
    partId,
    PartDone,
    CASE Substring(PartDone, 1, 1)
        WHEN '0' THEN 'no'
        WHEN '1' THEN 'yes'
    END As [first],
    CASE Substring(PartDone, 2, 1)
        WHEN '0' THEN 'no'
        WHEN '1' THEN 'yes'
    END As [second],
    CASE Substring(PartDone, 3, 1)
        WHEN '0' THEN 'no'
        WHEN '1' THEN 'yes'
    END As [third],
    CASE Substring(PartDone, 4, 1)
        WHEN '0' THEN 'no'
        WHEN '1' THEN 'yes'
    END As [fourth]
FROM
    @tmp
;

NB: If it's always four numbers, you shouldn't define the field as nvarchar(50). You know it's never going to contain any "extended" characters, so you don't need the n prefix. You know the length will never be more than 4, so you don't need room for 50 characters. And you know the length will never be less than 4, so you don't need a variable-length field.

Define the field as char(4) instead.

A better option would be to define four bit fields, and make the PartDone field a calculated column:

SQL

DECLARE @tmp TABLE 
(
    PartId int,
    [first] bit,
    [second] bit,
    [third] bit,
    [fourth] bit, 
    PartDone As
    (
        CASE WHEN [first] Is Null THEN '2' ELSE CAST([first] As char(1)) END
        + CASE WHEN [second] Is Null THEN '2' ELSE CAST([second] As char(1)) END
        + CASE WHEN [third] Is Null THEN '2' ELSE CAST([third] As char(1)) END
        + CASE WHEN [fourth] Is Null THEN '2' ELSE CAST([fourth] As char(1)) END
    )
);

INSERT INTO @tmp (PartId, [first], [second], [third], [fourth])
VALUES
    (555444, 0, 0, 1, NULL),
    (911877, 0, NULL, NULL, 1)
;

SELECT
    PartId,
    PartDone,
    CASE [first] WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END As [first],
    CASE [second] WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END As [second],
    CASE [third] WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END As [third],
    CASE [fourth] WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END As [fourth]
FROM 
    @tmp
;