SQL - concatenate fields that are not empty into a single string

I have a table tbl1

Name | account code | Type  | detail    | description
---------------------------------------------------------------------------
P1       | 12341              | Open | detail1  | description1
P2       | 12342              | Close | detail2  |
P3       | 12343              | Close |              | description2            
P4       | 12344              | Close |

I would like to do a Select that will selectively concatenate the information into a description if the information is available.

This is the logic I try to implement
if detail is not null and description is not null
Select Name + ' ' +  accountcode + ' ' +  Type + ' ' + detail + ' ' + description from tbl1
else if detail is  null and description is not null
Select Name + ' ' +  accountcode + ' ' +  Type + ' ' + description from tbl1
else if detail is  not null and description is null
Select Name + ' ' +  accountcode + ' ' +  Type + ' ' + detail from tbl1
else if detail is  null and description is null
Select Name + ' ' +  accountcode + ' ' +  Type + from tbl1

This is the result I hoping to get.
P1 12341 Open detail1description1
P2 12342 Close detail2
P3 12343 Close description2            
P4 12344 Close

How will I do that?
tommym121Asked:
Who is Participating?
 
micropc1Connect With a Mentor Commented:
This should be what you need... run this once to create the function...

CREATE FUNCTION getDetailDesc (@name varchar(10), @accountCode int, @type varchar(10), @detail varchar(10), @desc varchar(20))
RETURNS varchar(100)
AS
BEGIN
	DECLARE @detailDesc varchar(30)
	
	--if detail is not null and description is not null
	IF @detail IS NOT NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @detail + ']-[' + @desc + ']'
	
	--else if detail is null and description is not null
	IF @detail IS NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @desc + ']'

	--else if detail is not null and description is null
	IF @detail IS NOT NULL AND @desc IS NULL 
		SET @detailDesc = '[' + @detail + ']'
		
	--else if detail is null and description is null
	IF @detail IS NULL AND @desc IS NULL 
		SET @detailDesc = NULL

	RETURN @name + ' ' + CAST(@accountCode AS VARCHAR(10)) + RTRIM(' ' + ISNULL(@type, '')) + RTRIM(' ' + ISNULL(@detailDesc, ''))
END;

Open in new window


then use use it like this...

SELECT name, accountCode, [type], detail, [description], dbo.getDetailDesc(name, accountCode, [type], detail, [description]) AS detailDesc FROM tbl1

Open in new window

0
 
micropc1Commented:
This may work...

Select Name + ' ' +  CAST(accountcode AS VARCHAR(10)) + ' ' + Type + RTRIM(ISNULL(' ' + detail, ' ') + ISNULL(' ' + description, '')) from tbl1

Open in new window

0
 
arnoldCommented:
are the empty fields empty or are they null?
select (name + ' ' + accountcode +' '+ type + ' ' + isnull(detail,'')+isnull(description,'') ) from tbl1
if the fields are empty, empty field cat with an empty field leaves an empty field.

This presumes all the data types are similar.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
TempDBACommented:
What is the datatype for AccountCode. If it is a numeric\integer field, you have to cast it to a varchar... If not then just see what field is nullable and while concatinating the field, just check for isnull(fieldname,''). This will help eliminate the side effect of a field being null because anything added to null is always null...
0
 
micropc1Commented:
Arnold, i think you made your post after I edited mine. Look again... I'm using RTRIM to check for blanks and casting accountcode to a varchar.
0
 
tommym121Author Commented:
The field can be null and empty
The account code is numeric

What if I need to bracket detail or description in the result when they are present, How do you handle it.  Or I need to put a '-' if both detail or description are present.

Is there a programming mechanism like traditional programming language (e.g. 'C') that I can loop through to create a format string I need?
0
 
arnoldCommented:
What is the reason for catcatnating the various items?
You can Output the data as you see fit without going through this complexity on the SQL server.
I.e. run the select columns for table
 Then output the response as you see fit.
0
 
micropc1Commented:
You could do this with a cursor... a little lengthy, but it can be done...

DECLARE @name varchar(10)
DECLARE @accountCode int
DECLARE @type varchar(10)
DECLARE @detail varchar(10)
DECLARE @desc varchar(10)
DECLARE @detailDesc varchar(100)

CREATE TABLE #tmpTbl
	( val varchar(200))

DECLARE tbl1Cursor CURSOR FOR  
SELECT name, accountcode, [type], detail, description
FROM tbl1

OPEN tbl1Cursor   
FETCH NEXT FROM tbl1Cursor 
INTO @name, @accountCode, @type, @detail, @desc

WHILE @@FETCH_STATUS = 0   
BEGIN   

	--if detail is not null and description is not null
	IF @detail IS NOT NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @detail + ']-[' + @desc + ']'
	
	--else if detail is null and description is not null
	IF @detail IS NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @desc + ']'

	--else if detail is not null and description is null
	IF @detail IS NOT NULL AND @desc IS NULL 
		SET @detailDesc = '[' + @detail + ']'
		
	--else if detail is null and description is null
	IF @detail IS NULL AND @desc IS NULL 
		SET @detailDesc = NULL
	
	--add the item to a temp table
	INSERT INTO #tmpTbl
	VALUES (@name + ' ' + CAST(@accountCode AS VARCHAR(10)) + ' ' + @type + ' ' + @detailDesc)

	FETCH NEXT FROM tbl1Cursor 
	INTO @name, @accountCode, @type, @detail, @desc  
END   

CLOSE tbl1Cursor   
DEALLOCATE tbl1Cursor 

--select all the rows from the temp table and drop it
SELECT * FROM #tmpTbl
DROP TABLE #tmpTbl

Open in new window

0
 
tommym121Author Commented:
micropc1,

Can it be create a function (FunctionX)  that will pass name, accountcode, [type], detail, description as arguements and will return a string  so we can call this function in the select statement.

Select name, accountcode, [type], detail, description, FunctionX (name, accountcode, [type], detail, description)  from tbl1

Is this possible at all?
0
 
micropc1Commented:
Yes, but I'm on my iPhone at the moment. I can put something together later tonight when I'm near my computer. You'll basically want to put those IF blocks in a CREATE FUNCTION statement that returns the result as a varchar.
0
 
tommym121Author Commented:
Micropc1,

Thanks.  I did not mean to get you away from your important phone call :)
I do appreciate you help as I am not proficient in SQL yet.  Thanks again.
0
 
micropc1Commented:
Lol...no, not on a call...i mean I'm not near a computer and i'm typing this on my iPhone. Kindof hard to do a lot of typing on this small screen. :)
0
 
tommym121Author Commented:
THanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.