July 6, 2017 at 4:39 am
Hi there,
I've tried all kind of methods but I just can't get sql to trim the last "," from my string. I'm sure I could re-write the script not to add the last "," where the column_id is MAX(column_id)
But I wondered if you guys had any other thoughts as to why its not working. No matter what I try it won't trim the last character. Put your own object_id in there and give it a try.
--SET ANSI_PADDING OFF
--SET ANSI_PADDING ON
declare @maxcounter smallint = NULL
declare @counter smallint = 0
declare @string varchar(MAX) = NULL
declare @column_name varchar(128) = NULL
set @maxcounter = (select MAX(column_id) from sys.columns where object_id = 1248059532)
while @counter <= @maxcounter
begin
set @counter = @counter + 1
set @column_name = (select name from sys.columns where object_id = 1248059532 and column_id = @counter)
--select @column_name
set @string = concat(@string, @column_name, ',')
--select @string
end
select left(@string,DATALENGTH(@string)-1)
--select left(@string,LEN(@string)-2)
--select left(@string,LEN(@string)-100)
--select STUFF(@String,DATALENGTH(@String), 1, '')
--select SUBSTRING(@String,1, DATALENGTH(@String)-1)
--set @string = (select left(@string,DATALENGTH(@string)-12))
--set @string = (select STUFF(@String,DATALENGTH(@String), 1, ''))
--set @string = (select SUBSTRING(@String,1, DATALENGTH(@String)-1))
--SET @STRING = LEFT(@string, LEN(@string) -1)
--select @string
July 6, 2017 at 4:59 am
There must be something strange with the way the column names are stored or something , but cause this statement works for my colleague on a different DB when he changes the script to -2 characters it works and removes the ,
declare @maxcounter smallint = NULL
declare @counter smallint = 0
declare @string varchar(MAX) = NULL
declare @column_name varchar(128) = NULL
set @maxcounter = (select MAX(column_id) from sys.columns where object_id = 1248059532)
while @counter <= @maxcounter
begin
set @counter = @counter + 1
set @column_name = (select name from sys.columns where object_id = 1248059532 and column_id = @counter)
--select @column_name
set @string = concat(@string, @column_name, ',')
--select @string
end
select left(@string,DATALENGTH(@string)-2)
select left(@string,LEN(@string)-2)
select left(@string,LEN(@string)-100)
select STUFF(@String,DATALENGTH(@String), 2, '')
select SUBSTRING(@String,1, DATALENGTH(@String)-2)
July 6, 2017 at 5:17 am
The column name is of the data type sysname which is nvarchar(128), the data length will return the number of bytes which is double the number of characters, change the last statement into LEN instead of DATALENGTH.
😎
You can achieve the same output by using FOR XML concatenation and STUFF out the unwanted character.
July 6, 2017 at 5:55 am
Complementing what Eirikur said, you should read this article: Creating a comma-separated list (SQL Spackle) - SQLServerCentral
Here's an example on what you're trying to achieve. Note that it's easier to remove the comma when it's always on the same position.
SELECT t.name,
STUFF(( select ',' + c.name
from sys.columns c
where c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM sys.tables t
GROUP BY t.object_id, t.name;
July 6, 2017 at 6:18 am
Firstly thank you very much for your help,
However what's very interesting is that I still get a comma at the end of the string even with your script.
SELECT t.name,
STUFF(( select ',' + c.name
from sys.columns c
where c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM sys.tables t
where object_id = 1248059532
GROUP BY t.object_id, t.name
July 6, 2017 at 6:22 am
Forget what I said , I'm being a dope. there are more columns than my result set is displaying, that's why I'm seeing a comma at the end, because there a more columns following that. :Whistling::Whistling::Whistling:
July 6, 2017 at 6:31 am
SQL Bandit - Thursday, July 6, 2017 6:18 AMFirstly thank you very much for your help,
However what's very interesting is that I still get a comma at the end of the string even with your script.
SELECT t.name,
STUFF(( select ',' + c.name
from sys.columns c
where c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM sys.tables t
where object_id = 1248059532
GROUP BY t.object_id, t.name
Check again, ran both scripts and no comma neither at the beginning nor the end
😎
SELECT t.name,
STUFF(( select ',' + c.name
from sys.columns c
where c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM sys.tables t
WHERE t.name = 'Transactions'
AND t.schema_id = SCHEMA_ID(N'dbo')
GROUP BY t.object_id, t.name;
SELECT t.name,
STUFF(( select ',' + c.name
from sys.columns c
where c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM sys.tables t
where object_id = OBJECT_ID(N'dbo.Transactions')
GROUP BY t.object_id, t.name
name
--------------- -----------------------------------------
Transactions TransID,CustID,TransType,TransDate,Amount
name
--------------- -----------------------------------------
Transactions TransID,CustID,TransType,TransDate,Amount
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply