September 5, 2011 at 6:25 am
Hi guys,
Could you please help me take a look at the query below, I don't know why the output is not 'b2,b1,a3,a2,a1'. I only want to remove the dup one...
Thanks
Lindsay
declare @colname varchar(100)
Set @colname = 'a1,a2,a3,b1,b2,a1'
declare @colout nvarchar(100)
declare @sql nvarchar(4000)
set @sql = 'Select @colstr = '',''+ CAST(col AS NVARCHAR(100)) + @colstr from (Select distinct * from (select '+ '''' + replace(@colname,',',''' col union all select ''')+''') as tbl1) as tbl2'
exec sp_executesql @sql ,N'@colstr nvarchar(25) OUTPUT',@colstr = @colout OUTPUT;
select @colout
select stuff(@colout,1,1,'')
September 5, 2011 at 6:37 am
Try using the PRINT command to see what SQL you will be executing. It might be obvious what the problem is if you do that. In the absence of any table DDL or sample data, it's difficult for us to provide any more specific assistance than that.
John
September 5, 2011 at 7:10 am
I presume you are questioning why you are getting NULL in the output.
If so, you need to initialise @colout. Unless you set the CONCAT_NULL_YIELDS_NULL option to off, NULL concatenated with something results in NULL
declare @sql nvarchar(4000)
set @colout = ''
set @sql = 'Select @colstr = '',''+ CAST(col AS NVARCHAR(100)) + @colstr from....
September 5, 2011 at 7:52 am
declare @colname varchar(100)
Set @colname = 'a1,a2,a3,b1,b2,a1'
set @colname = '<R><N>' + replace(@colname,',','</N><N>') + '</N></R>'
declare @xml xml = (select cast(@colname as xml))
--use this if you want colnames as separate rows
;with cte as
(
select c.value('(.)[1]','char(2)') [col] from @xml.nodes('//N') as tab(c)
)
select col from cte group by col
--use this if you want colnames as csv
;with cte as
(
select c.value('(.)[1]','char(2)') [col] from @xml.nodes('//N') as tab(c)
)
select stuff((select ',' + col from cte group by col for xml path('')),1,1,'')
- arjun
https://sqlroadie.com/
September 5, 2011 at 8:12 pm
Thanks all of your help.
arjun,
Your code is perfect. But I fund it doesn't work if my string include "&" (like Set @colname = 'a2soft,a1soft,a3,b1,b2,a1soft.abc&test'), how to work arround it?
Thanks
Lindsay
September 5, 2011 at 8:41 pm
I found there is one way to work arround it, we can use & for XML format to replace it. is there any other better way?
September 5, 2011 at 9:51 pm
Replace '&' by '& amp ;' (without the spaces in between) in the source string. Same thing applies for any '<' & '>'s in your texts, these need to be replaced by '& lt ;' and '& gt ;' respectively.
September 5, 2011 at 10:20 pm
Cool, you found a workaround!! 🙂 I'm glad that it was helpful. This is a very fast way to convert csv to rows. Have fun.
- arjun
https://sqlroadie.com/
September 6, 2011 at 9:12 pm
Thanks all your help^_^
September 7, 2011 at 2:55 am
If converting to XML works for you, then fine, but the very simple solution to your problem as just to add an IsNull() within the SQL that you're executing:
declare @colname varchar(100)
Set @colname = 'a1,a2,a3,b1,b2,a1'
declare @colout nvarchar(100)
declare @sql nvarchar(4000)
set @sql = 'Select @colstr = '',''+ CAST(col AS NVARCHAR(100)) + IsNull(@colstr,'''') from (Select distinct * from (select '+ '''' + replace(@colname,',',''' col union all select ''')+''') as tbl1) as tbl2'
print @sql
exec sp_executesql @sql ,N'@colstr nvarchar(25) OUTPUT',@colstr = @colout OUTPUT;
select @colout
select stuff(@colout,1,1,'')
September 7, 2011 at 2:58 am
If you put the comma into the IsNull() as well, you can lose the final stuff() as well...
declare @colname varchar(100)
Set @colname = 'a1,a2,a3,b1,b2,a1'
declare @colout nvarchar(100)
declare @sql nvarchar(4000)
set @sql = 'Select @colstr = CAST(col AS NVARCHAR(100)) + IsNull('',''+@colstr,'''') from (Select distinct * from (select '+ '''' + replace(@colname,',',''' col union all select ''')+''') as tbl1) as tbl2'
print @sql
exec sp_executesql @sql ,N'@colstr nvarchar(25) OUTPUT',@colstr = @colout OUTPUT;
select @colout
--select stuff(@colout,1,1,'')
September 7, 2011 at 3:27 am
paul_ramster (9/7/2011)
If converting to XML works for you, then fine, but the very simple solution to your problem as just to add an IsNull() within the SQL that you're executing:
declare @colname varchar(100)Set @colname = 'a1,a2,a3,b1,b2,a1'
declare @colout nvarchar(100)
declare @sql nvarchar(4000)
set @sql = 'Select @colstr = '',''+ CAST(col AS NVARCHAR(100)) + IsNull(@colstr,'''') from (Select distinct * from (select '+ '''' + replace(@colname,',',''' col union all select ''')+''') as tbl1) as tbl2'
print @sql
exec sp_executesql @sql ,N'@colstr nvarchar(25) OUTPUT',@colstr = @colout OUTPUT;
select @colout
select stuff(@colout,1,1,'')
And also this one needs some characters escaped: single quotes in the values would mess up the generated dynamic T-SQL code, i.e. you need to replace any '-characters in your values by 2 '-characters before putting them into the statement.
declare @colname varchar(100)
Set @colname = 'a1,a2,a3,b1,b2,a1'
declare @colout varchar(100)
declare @sql nvarchar(max)
set @sql = 'Select @colstr = CAST(col AS VARCHAR(100)) + isnull('','' + @colstr, '''') from (Select distinct * from (select '+ '''' + replace(replace(@colname, '''', ''''''),',',''' col union all select ''')+''') as tbl1) as tbl2'
select @sql;
exec sp_executesql @sql ,N'@colstr nvarchar(100) OUTPUT',@colstr = @colout OUTPUT;
select @colout
September 7, 2011 at 4:11 am
Yes indeed. And the column names should really be quoted to combat SQL Injection
declare @colname varchar(100)
Set @colname = 'a1,a''2,a[3,b1,b2,a1'
declare @colout nvarchar(100)
declare @sql nvarchar(4000)
set @sql = 'Select @colstr = CAST(col AS NVARCHAR(100)) + IsNull('',''+@colstr,'''') from (Select distinct * from (select '+ '''[' + replace(replace(replace(@colname,'[','[['),'''',''''''),',',']'' col union all select ''[')+']'') as tbl1) as tbl2'
print @sql
exec sp_executesql @sql ,N'@colstr nvarchar(100) OUTPUT',@colstr = @colout OUTPUT;
select @colout
September 14, 2011 at 8:29 am
Thank you R.P.Rozema and Paul. This code can answer why my initial query was wrong. Thanks again.
Thanks
Lindsay
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply