Could you please help me take a look at the sql query

Question

Could you please help me take a look at the sql query

lindsaywang

SSCrazy

Points: 2569
More actions
September 5, 2011 at 6:25 am

#244259

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,'')

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

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....

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 3

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/

lindsaywang SSCrazy Points: 2569 More actions · Answer 4

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

lindsaywang SSCrazy Points: 2569 More actions · Answer 5

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?

R.P.Rozema SSChampion Points: 12305 More actions · Answer 6

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.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 7

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/

lindsaywang SSCrazy Points: 2569 More actions · Answer 8

lindsaywang

SSCrazy

Points: 2569

September 6, 2011 at 9:12 pm

#1377767

Thanks all your help^_^

paul_ramster Ten Centuries Points: 1382 More actions · Answer 9

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,'')

paul_ramster Ten Centuries Points: 1382 More actions · Answer 10

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,'')

R.P.Rozema SSChampion Points: 12305 More actions · Answer 11

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

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

paul_ramster Ten Centuries Points: 1382 More actions · Answer 12

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

lindsaywang SSCrazy Points: 2569 More actions · Answer 13

Thank you R.P.Rozema and Paul. This code can answer why my initial query was wrong. Thanks again.

Thanks

Lindsay