Add comma to end of result for dynamic query

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Highlighted
Enthusiast

Add comma to end of result for dynamic query

Hi All,

 

I am dynamically generating part of a query through a macro and i want to append comma at the end of each line except the last line else it would give syntax error so is threre a way to do this.

 

Query

SEL  COLUMNNAME

FROM DBC.COLUMNVS

WHERE TABLENAME = 'TABLE-NAME'

 

Output

COL1

COL2

COL3

 

EXPECTED OUTPUT

COL1,

COL2,

COL3

 

If i append comma(,) at the end as below then it would come after all the columns including the last column also

 

SEL  COLUMNNAME  ||  ','

FROM DBC.COLUMNVS

WHERE TABLENAME = 'TABLE-NAME'

 

Please suggest.

 

Thanks


Accepted Solutions
Teradata Employee

Re: Add comma to end of result for dynamic query

Prefix columname with the comma for all columns except columnid = 1025 ( the first col7mn )

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Add comma to end of result for dynamic query

Prefix columname with the comma for all columns except columnid = 1025 ( the first col7mn )

Teradata Employee

Re: Add comma to end of result for dynamic query

You can also evaluate if it's the last column using ColumnID :

 

SELECT ColumnName || case row_number() over(partition by DatabaseName, TableName order by ColumnId desc) when 1 then '' else ',' end
  FROM DBC.ColumnsV
 WHERE TableName = 'TABLE-NAME';

 

Enthusiast

Re: Add comma to end of result for dynamic query

Thanks David and Walder. That solved it.