I am trying to split name values to analyze them afterwards. To do so, I generate 15 columns in T-SQL and then use an UDF (https://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx) to split a column 'value' into the new columns (using space as separator). I get the following results, as intended, so far, so good...
id|value |col_1|col_2|col_3|col_4|col_5|col_6|col_7|col_8|col_9|col_10|col_11|col_12|col_13|col_14|col_15
1 |xxx |x |x |x | | | | | | | | | | | |
2 |x |x | | | | | | | | | | | | | |
3 |xxxxxxx|x |x |x |x |x |x |x | | | | | | | |
4 |xxxx |x |x |x |x | | | | | | | | | | |
Happy with my solution I started on the code to really analyze the seperate parts. My colleague however, came up with a small nonsense-note, he said the columns that are not used, should be removed. And because I am appointed to help colleagues instead of telling them how silly their questions are, I said I'd look into it.
The thing is that my ego does not allow me to use 15 hard-coded lines for this. I'd really like to do this with a cursor, so that the code keeps working if later on I need to parse much longer strings (which btw is actually assumable). I use a number in the cursor to create a column name. Then I thought it would be a good idea to use a 'select 1 from #1 where len(column_name)'-query to see if the column is in use. But now I am stuck creating the dynamic sql that include the created column_name. Which is a problem, because I need that same column_name to actually remove it when found empty.
while @count < @max_nr_columns
begin
set @colname = 'part' + convert(varchar(255), (@count + 1))
declare @max_col_real nvarchar(4000)-- = N'max_col_real_is_empty'
declare @mc int = 0
exec('sp_executesql (select 1 from #1 where ' + @colname + ' is not null), N''@max_col_real nvarchar(4000) output'', @max_col_real output')
set @max_col_real = (@sql)
set @count = @count + 1
end
print @max_col_real
It throws an error about a comma, which means that the problem resides in the exec-line. Can anyone point me in the right direction for removing column 8-15? Or tell me what I am doing wrong in the above code?
I am using SQL2014, but like to stay downwards compatible until 2008.
You can pivot to suppress null values. Pivot,Stuff and FOR XML should be available since 2008.
So example could be
SQL Code UPDATE with DYNAMIC UNPIVOT
Create table #temp (id int, value nvarchar(50),col_1 nvarchar(50),col_2 nvarchar(50),col_3 nvarchar(50),col_4 nvarchar(50),col_5 nvarchar(50),col_6 nvarchar(50),col_7 nvarchar(50),col_8 nvarchar(50),col_9 nvarchar(50))
insert into #temp
values
(1 ,'xxx' ,'x','x','x',null,null,null,null,null,null),
(2 ,'x' ,'x',null,null,null,null,null,null,null,null),
(3 ,'xxxxxxx','x','x','x','x','x','x','x',null,null),
(4 ,'xxxx' ,'x','x','x','x',null,null,null,null,null)
declare @gruppe nvarchar(max)
declare @gruppeSql nvarchar(max)
declare @SQL nvarchar(max)
DECLARE myCustomers CURSOR FOR
Select [Name] from tempdb.sys.columns where object_id = object_id('tempdb..#temp')
and [name] like 'col%'
set @gruppeSql = ''
OPEN myCustomers
FETCH NEXT FROM myCustomers INTO @gruppe
IF (@@FETCH_STATUS>=0)
BEGIN
SET @gruppeSql = @gruppeSql +'[' +@gruppe+']'
FETCH NEXT FROM myCustomers INTO @gruppe
END
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF (@@FETCH_STATUS<>-2)
SET @gruppeSql = @gruppeSql + ',[' +@gruppe+']'
FETCH NEXT FROM myCustomers INTO @gruppe
END
CLOSE myCustomers
DEALLOCATE myCustomers
SET @gruppeSql = replace(@gruppesql,'''','')
print @gruppeSql
--select @gruppeSql
SET @SQL = '
SELECT distinct Pets = ''Select'' + STUFF((SELECT N'', '' + cols
FROM (select distinct cols from #temp a
UNPIVOT (Rowno for Cols in('+@gruppeSql+')) as pvy ) AS p2
FOR XML PATH(N'''')), 1, 2, N'' '')'
print @sql
exec(@sql)
drop table #temp