Link to home
Start Free TrialLog in
Avatar of vhaum
vhaumFlag for United States of America

asked on

SQL function with coalesce not returning first column

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vhaum

ASKER

Thanks, I was thinking it was null, I was having a hard time remembering where to set it to ''.

Thanks