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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
micropc1Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tommym121Author Commented:
THanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.