Solved

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

Posted on 2012-03-17
13
437 Views
Last Modified: 2012-03-20
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?
0
Comment
Question by:tommym121
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37733981
This may work...

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

Open in new window

0
 
LVL 77

Expert Comment

by:arnold
ID: 37734127
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 37734673
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 7

Expert Comment

by:micropc1
ID: 37734680
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
 

Author Comment

by:tommym121
ID: 37734804
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
 
LVL 77

Expert Comment

by:arnold
ID: 37734836
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37734871
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
 

Author Comment

by:tommym121
ID: 37735402
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37735558
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
 

Author Comment

by:tommym121
ID: 37735617
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
 
LVL 7

Expert Comment

by:micropc1
ID: 37735627
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
 
LVL 7

Accepted Solution

by:
micropc1 earned 500 total points
ID: 37736142
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
 

Author Closing Comment

by:tommym121
ID: 37741910
THanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Re-appearing SQL Server Agent jobs 7 30
SQL Activity Monitor detail 2 28
sql, case when & top 1 14 30
Need to find substring in SQL 5 14
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question