Select records from multiple columns into one in SQL Server

smile
 
on Oct 22, 2022 03:45 AM
358 Views

I have a following data

ID

Question

Is_Yes

Is_No

Is_Clear

Is_Yes1

Is_No1

Is_Clear1

1

Sentence one.

Ok

 

 

 

No

 

2

Sentence two

 

No

 

Ok

 

 

3

Sentence three

Ok

 

 

 

 

Unclear

4

Sentence four

 

 

Unclear

 

No

 

5

Sentence five

 

No

 

Ok

 

 

Required output

ID

Question

Remarks

Status

1

Sentence one.

Ok

No

2

Sentence two

No

Ok

3

Sentence three

Ok

Unclear

4

Sentence four

Unclear

No

5

Sentence five

No

Ok

 how to get soution please.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 22, 2022 01:16 PM
on Oct 22, 2022 01:16 PM

Hi smile,

Use COALESCE function.

Refer below query.

SQL

CREATE TABLE #Questions
(
    [ID] INT ,
	[Question] VARCHAR (20) ,
	[Is_Yes] VARCHAR (20),
	[Is_No] VARCHAR (20),
	[Is_Clear] VARCHAR (20),
	[Is_Yes1] VARCHAR (20),
	[Is_No1] VARCHAR (20),
	[Is_Clear1] VARCHAR (20),
)
  INSERT INTO #Questions VALUES (01,'Sentence one.','Ok','','','','No','')
  INSERT INTO #Questions VALUES (02,'Sentence tow.','','No','','Ok','','')
  INSERT INTO #Questions VALUES (03,'Sentence three.','Ok','','','','','Unclear')
  INSERT INTO #Questions VALUES (04,'Sentence four.','','','Unclear','','No','')
  INSERT INTO #Questions VALUES (05,'Sentence five.','','No','','Ok','','')
 

SELECT [ID],[Question],
	COALESCE(NULLIF(Is_Yes,''), COALESCE(NULLIF(Is_No,''), Is_Clear)) AS 'Remark',
	COALESCE(NULLIF(Is_Yes1,''), COALESCE(NULLIF(Is_No1,''), Is_Clear1)) AS 'Status'
FROM #Questions

DROP TABLE #Questions

Output

ID

Question

Remarks

Status

1

Sentence one.

Ok

No

2

Sentence two.

No

Ok

3

Sentence three.

Ok

Unclear

4

Sentence four.

Unclear

No

5

Sentence five.

No

Ok