How to get columns listed in a single row using coalesce in SQL?

Question

1.00/5 (1 vote)

See more:

I am using a dynamic query and trying to fetch columns in a single row using COALESCE function. But it is not working

What I have tried:

DECLARE @COLUMNS NVARCHAR(3000)
SET @COLUMNS = 'SELECT
COALESCE('+@COLUMNS+' + '','', '') +
CM.ColumnName
FROM ICCS_StdReportTable_Master TM
inner join
ICCS_StdReport_Column_Master CM
on CM.TableId=TM.TableId
where
tm.RecordIdentifierKeyword in (
select SUBSTRING(VALUE,TM.StartPosition,TM.Length) from ICCS_MENUS MNU
INNER JOIN
ICCS_StdReportTable_Master TM
ON TM.TableName=SUBSTRING(VALUE,TM.StartPosition,TM.Length)
INNER JOIN
ICCS_SCREENS SC
ON SC.SCREENID=MNU.SCREENID'

Posted 26-Feb-17 17:30pm

Alex2 101

Updated 27-Feb-17 3:57am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-02-27T03:57:00

"not working" is not very helpful and makes it harder for us to help you! It's always a good idea to make the query as simple as possible first - get something working and then add the complexity later. That way you could include some sample data and the table schemas. These things make your question better meaning it is more likely to get a quick answer.

To your problem though ... I will demonstrate what is going on with this very simple table:

SQL

eate table [ColumnNames]
(
	id INT IDENTITY(1,1),
	ColumnName varchar(125)
)
INSERT INTO ColumnNames values ('COL1'),('COL2'),('COL3')

If I do something similar to your query I get this query:

SQL

DECLARE @columns NVARCHAR(MAX) = 'SELECT '
SELECT @columns = COALESCE(@columns+',' ,'') + CAST(ColumnName AS Varchar)
FROM ColumnNames
PRINT @columns

But when I run that query I get the results:

SELECT ,COL1,COL2,COL3

Spot that extra comma after the SELECT but before COL1. It's going to cause a syntax error in the final query. You shouldn't be surprised that it's there as you are trying to add a comma to the end of @columns for each value returned from the table! If @columns already contains data then SQL will put the comma on the end of that first.

If I change the query to this

DECLARE @columns NVARCHAR(MAX) = null
SELECT @columns = COALESCE(@columns+',' ,'') + CAST(ColumnName AS Varchar)
FROM ColumnNames

then I get

COL1,COL2,COL3

The problem now is how to get the rest of your query together. My personal preference is to have a separate variable for the column list and the final sql e.g.

SQL

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT ' + @columns + ' ... the rest of your query'

Even if you don't want to do that it is still a two stage query -

SQL

DECLARE @columns NVARCHAR(MAX) = null
SELECT @columns = COALESCE(@columns+',' ,'') + CAST(ColumnName AS Varchar)
FROM ColumnNames
set @columns = 'SELECT ' + @columns + '... the rest of your query'