Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Serial Number with COALESCE function

Posted on 2013-11-10
Medium Priority
235 Views
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
``````
To Generate :
``````[AccNo], [Attach], [BankName], [Branch], [BranchSer], [CName], [City], [CriditPeriod], [CusPO], [DiffDay]
``````

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
``````

thanks,
0
Question by:ethar1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 35

Accepted Solution

Robert Schutt earned 2000 total points
ID: 39636691
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
``````
0

LVL 49

Expert Comment

ID: 39637575
just as a tip, you can use QUOTENAME

e.g.
SELECT top 10 @Names = COALESCE(@Names +', ', '') + QUOTENAME(COLUMN_NAME)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll