Solved

Serial Number with COALESCE function

Posted on 2013-11-10
2
224 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
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 48

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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?
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 shrink a transaction log file down to a reasonable size.

830 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