Need Help SQL Query

Question

Post reply

Need Help SQL Query

dprasannain

SSC Enthusiast

Points: 176
More actions
December 10, 2015 at 5:49 am

#306141

Hello Experts,
I have a table with EmployeeNames as below which is a varchar column
EmployeeNames
---------------
A
B
C
D
How do i get the output as 'A','B','C','D' using coalesce function. Kindly help

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Why the coalesce function, does the table contain nulls in the column?

Given that its an employee table I would be shocked to find a employee with no name.

What it looks like you need is some form of pivot if you want to get the list of employees in a comma separated string.

dprasannain SSC Enthusiast Points: 176 More actions · Answer 2

The above case is just a scenario , is there any possibility to get the desired output..? If how

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

COALESCE will work, you just need to ensure that you escape any nulls in the table correctly with an ISNULL or take a look at FOR XML PATH

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

dprasannain (12/10/2015)
Hello Experts,
I have a table with EmployeeNames as below which is a varchar column
EmployeeNames
---------------
A
B
C
D
How do i get the output as 'A','B','C','D' using coalesce function. Kindly help

Is the use of the COALESCE function mandatory in this case or do you have some flexibility?

Also, does the output have to be 'A','B','C','D' or can it be A,B,C,D?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dprasannain SSC Enthusiast Points: 176 More actions · Answer 5

Use of coalesce is not mandatory however the output has to be in the same format like 'A', 'B', 'C', 'D'

Ant-Green SSC Guru Points: 113445 More actions · Answer 6

It all comes down to how you want to handle nulls

First COALESCE returns a null due to the null in the table

Second COALESCE returns a string but with *** in it due to the null, replace the *** with however you want to handle nulls

Last is XML PATH which ignores the null altogether

create table #emps (EN char(1))

insert into #emps values

('A'),

('B'),

('C'),

('D'),

(NULL)

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + char(39)+en+char(39)

FROM #emps

select @names

SELECT @Names = COALESCE(@Names + ', ', '') + ISNULL(char(39)+en+char(39),'***')--WHAT DO YOU WANT TO DO HERE IF THERE IS A NULL IN THE TABLE??????

FROM #emps

select @names

SELECT Stuff(

(SELECT N', ' + char(39)+en+char(39) FROM #emps FOR XML PATH(''),TYPE)

.value('text()[1]','nvarchar(max)'),1,2,N'')

drop table #emps

dprasannain SSC Enthusiast Points: 176 More actions · Answer 7

Why doesn't this query return's any value . Whats wrong with this query

Select * from #emps Where EN IN (@Names)

Ant-Green SSC Guru Points: 113445 More actions · Answer 8

Post the full code you have executed, that code isn't in my snippet so you have modified it some how to work with your environment

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

dprasannain (12/10/2015)
Why doesn't this query return's any value . Whats wrong with this query
Select * from #emps Where EN IN (@Names)

- because @Names isn't the comma-delimited list of string values which IN expects, it's a single string value. Your query is logically equivalent to this:

Select * from #emps Where EN = @Names

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dprasannain SSC Enthusiast Points: 176 More actions · Answer 10

create table #emps (EN varchar(10))

insert into #emps values

('A'),

('B'),

('C'),

('D')

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + char(39)+en+char(39)

FROM #emps

print @Names

-- This query does'nt return any values when using with IN clause

Select * from #emps Where EN IN (@Names)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Yes, you'd need to use dynamic SQL, something like this:

DECLARE @sql nvarchar(200)

SET @sql = N'Select * from #emps Where EN IN (' + @names + N')'

EXEC sp_executesql @sql

John

Ant-Green SSC Guru Points: 113445 More actions · Answer 12

dprasannain (12/10/2015)
create table #emps (EN varchar(10))
insert into #emps values
('A'),
('B'),
('C'),
('D')
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + char(39)+en+char(39)
FROM #emps
print @Names
-- This query does'nt return any values when using with IN clause
Select * from #emps Where EN IN (@Names)

Why are you searching the table again for all the records that you have just pulled out? You might as well just do SELECT * FROM #EMPS

Can you explain the bigger picture here?