如何在SQL中使用coalesce在单行中列出列？ [英] How to get columns listed in a single row using coalesce in SQL?

问题描述

我正在使用动态查询并尝试使用COALESCE函数获取单行中的列。但它不起作用



我尝试过：



DECLARE @COLUMNS NVARCHAR（3000）

SET @COLUMNS ='SELECT

COALESCE（'+ @ COLUMNS +'+''，''，''）+

CM.ColumnName

来自ICCS_StdReportTable_Master TM

内部联接

ICCS_StdReport_Column_Master CM

on CM.TableId = TM.TableId

其中

tm.RecordIdentifierKeyword in（

从ICCS_MENUS MNU选择SUBSTRING（VALUE，TM.StartPosition，TM.Length）

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'

解决方案

不工作不是很有帮助，让我们更难帮助你！首先使查询尽可能简单 - 让某些工作变得有效然后再添加复杂性总是一个好主意。这样您就可以包含一些示例数据和表模式。这些事情使你的问题变得更好意味着它更有可能得到一个快速答案。



对你的问题虽然...我将展示这是怎么回事简单的表格：

 eate  table  [ColumnNames] 
（
 id  INT   IDENTITY （ 1 ， 1 ），
 ColumnName  varchar （ 125 ）
）
  INSERT   INTO  ColumnNames 值（'  COL1'），（'   COL2'），（'  COL3' ）

如果我做了类似于你的查询的事情，我会得到这个查询： DECLARE @ columns NVARCHAR （MAX）= ' SELECT'
SELECT @ columns = COALESCE （@ columns + ' ，'，' ' ）+ CAST（ColumnName AS Varchar ）
FROM ColumnNames
PRINT @ columns

但是当我运行该查询时，我得到的结果是：

 SELECT，COL1，COL2，COL3 

在 SELECT之后发现额外的逗号但在 COL1 之前。它会在最终查询中导致语法错误。您不应该感到惊讶，因为您正在尝试为表中返回的每个值添加一个逗号到 @columns 的末尾！如果 @columns 已经包含数据，那么SQL将把逗号放在第一个结尾。



如果我将查询更改为此

 DECLARE @columns NVARCHAR（MAX）=  null  
 SELECT @columns = COALESCE（@ columns +'，'，''）+ CAST（ColumnName AS Varchar）
 FROM ColumnNames 

然后我得到

 COL1，COL2，COL3 

现在的问题是如何获得其余的一起查询。我个人的偏好是为列列表和最终的sql提供单独的变量，例如

  DECLARE   @ sql   VARCHAR （MAX）
  SET   @ sql  = '  SELECT' +  @ columns  + '  ...查询的其余部分' 

即使您不想这样做，它仍然是两阶段查询 -

  DECLARE   @ columns   NVARCHAR （MAX）=  null  
  SELECT   @ columns  =  COALESCE （@ columns + ' ，'，'  '）+ CAST（ColumnName  AS   Varchar ）
  FROM  ColumnNames 
  set   @ columns  = '  SELECT' +  @ columns  +   ...查询的其余部分' 

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'

解决方案

"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:

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:

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.

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 -

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

这篇关于如何在SQL中使用coalesce在单行中列出列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！