Solved

SQL function with  coalesce not returning first column

Posted on 2008-06-23
3
207 Views
Last Modified: 2010-04-21
I have the following function that returns a string for pivoting data.  This function also casts it based on a value from another field.

When I try this it doesn't return the first value but does return subsequent values. I.E. a project should have 4 columns but only comes back with three.
ALTER function [dbo].[column_elements_casted] ( @projectid int)

returns varchar(Max)

as

begin
 

declare @res varchar(Max)
 

      select @res = coalesce(@res + ', cast ([' +  ELEMENT_NAME + '] as ' + Element_Type_Name + ') as [' + ELEMENT_NAME + ']', '')-- + ELEMENT_NAME

      from dbo.Project_Element_Names

      WHERE Project_ID = '53'

      
 

select @res

      
 

END

Open in new window

0
Comment
Question by:vhaum
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
here we go:
ALTER function [dbo].[column_elements_casted] ( @projectid int)
returns varchar(Max)
as
begin
 
declare @res varchar(Max)
 
      select @res = coalesce(@res + ',' ,'' ) + cast ([' +  ELEMENT_NAME + '] as ' + Element_Type_Name + ') as [' + ELEMENT_NAME + ']' )
      from dbo.Project_Element_Names
      WHERE Project_ID = '53'
      
 
select @res
      
 
END

Open in new window

0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
Comment Utility
your @res variable is NULL, try this:

declare @res varchar(Max)
set @res = ''
0
 

Author Closing Comment

by:vhaum
Comment Utility
Thanks, I was thinking it was null, I was having a hard time remembering where to set it to ''.

Thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now