Solved

Serial Number with COALESCE function

Posted on 2013-11-10
2
233 Views
Last Modified: 2013-11-16
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
Comment
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
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 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

Open in new window

0
 
LVL 49

Expert Comment

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

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

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question