CODEWITHSUNDEEP
sqlsql-server
Nits Patel
Nits Patel

Reputation: 390

remove last comma from column

Here i have written some regarding expected output but its not working for last column all field should be end with comma excluding last column value i use something like this my code is give bellow 

declare @query varchar set @Query = Left(@Query,len(@Query)-1)
SELECT  '@' + COLUMN_NAME as c1,
CASE DATA_TYPE 

WHEN 'NVARCHAR' then 'nvarchar(max)' + ','
WHEN 'INT' then 'int'  + ','
WHEN 'DATETIME' then 'Datetime()' + ',' 
when 'Float' then 'float' + ','
end
as c2
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tbl_data'



c1           c2

@MastCode   float,
@BALCO_EMP_ID   float,
@MastName   nvarchar(max),
@FatherName nvarchar(max),
@Department nvarchar(max),
@PlantName  nvarchar(max),
@PO         nvarchar(max),
@PO_START   nvarchar(max),
@PO_END         nvarchar(max),
@Contractor nvarchar(max),
@ContractorCode nvarchar(max)

Upvotes: 0

Views: 61

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

You could place your current query logic into a CTE, and then handle the final comma using analytic functions:

WITH cte AS (
    SELECT '@' + COLUMN_NAME AS c1,
        CASE DATA_TYPE 
        WHEN 'NVARCHAR' THEN 'nvarchar(max)'
        WHEN 'INT' THEN 'int'
        WHEN 'DATETIME' THEN 'Datetime()'
        WHEN 'Float' THEN 'float' END AS c2,
        ROW_NUMBER() OVER (ORDER BY DATA_TYPE) AS rn,
        COUNT(*) OVER () AS cnt
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'tbl_data'
)

SELECT
    c1 As col1,
    c2 + CASE WHEN rn < cnt THEN ',' ELSE '' END AS col2
FROM cte;

Upvotes: 1

Related Questions