• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Serial Number with COALESCE function

Dear all,
I use The following :
DECLARE @Names VARCHAR(8000) ;DECLARE @i int=0; 
SELECT top 10 @Names = COALESCE(@Names +', ', '') + '[' + COLUMN_NAME+  ']' 
FROM dbo.site_DatabaseFields
where TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME  = 'JDB.dbo.Invoices'
SELECT @Names

Open in new window

To Generate :
[AccNo], [Attach], [BankName], [Branch], [BranchSer], [CName], [City], [CriditPeriod], [CusPO], [DiffDay]

Open in new window


I need to generate :
[AccNo] as Field1, [Attach] as Field2, [BankName] as Field3, [Branch] as Field4, [BranchSer] as Field5, [CName] as Field6, [City] as Field7, [CriditPeriod] as Field8, [CusPO] as Field9, [DiffDay] as Field10

Open in new window


thanks,
0
ethar1
Asked:
ethar1
1 Solution
 
Robert SchuttSoftware EngineerCommented:
Try this:
DECLARE @Names VARCHAR(8000) ;DECLARE @i int=0; 
SELECT top 10 @i = @i + 1, @Names = COALESCE(@Names +', ', '') + '[' + COLUMN_NAME+  '] as Field' + CONVERT(varchar, @i) 
FROM dbo.site_DatabaseFields
where TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME  = 'JDB.dbo.Invoices'
SELECT @Names

Open in new window

0
 
PortletPaulfreelancerCommented:
just as a tip, you can use QUOTENAME

e.g.
SELECT top 10 @Names = COALESCE(@Names +', ', '') + QUOTENAME(COLUMN_NAME)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now