Dynamic column result

Question

Hi,

I have table like below

create table ##temp (id nvarchar(50), Total decimal(18,2), paid decimal(18,2), paid1 decimal(18,2), paid2 decimal(18,2), code1 nvarchar(20),code2 nvarchar(20))

insert into ##temp values ('1001',1000, 100,50,20,'Code1','Code2');

insert into ##temp values ('1002',2000, 500,70,NULL,'Code1',NULL);

select * from ##temp;

drop table ##temp;

in the above table Paid1,Paid2.. and Code1,Code2.. column will be created dynamically. the columns will be increase/decrese.

i need the final result like below

create table ##result (id nvarchar(50), Total decimal(18,2), TotalPaid decimal(18,2), UnPaid decimal(18,2), LastCode nvarchar(20))

insert into ##result values ('1001',1000,170,830,'Code2');

insert into ##result values ('1001',2000,570,1430,'Code1');

select * from ##result;

drop table ##result;

how to achieve this?

Thanks

Answer 1

Hi @kasim mohamed ,

You have to create a dynamic query, something like this:

DECLARE @Columns_paid as VARCHAR(MAX)
DECLARE @Columns_code as VARCHAR(MAX)
DECLARE @SQL as VARCHAR(MAX)

SELECT @Columns_paid = COALESCE(@Columns_paid + ' + ','') + 'ISNULL('+ QUOTENAME(Name) + ',0)'
FROM
   (SELECT DISTINCT name
    FROM   tempdb.sys.columns
	WHERE  object_id = Object_id('tempdb..##temp')
	AND name like 'paid%'
   ) AS B
ORDER BY B.Name

SELECT @Columns_code = COALESCE(@Columns_code + ' , ','') + QUOTENAME(Name) 
FROM
   (SELECT DISTINCT name
    FROM   tempdb.sys.columns
	WHERE  object_id = Object_id('tempdb..##temp')
	AND name like 'code%'
   ) AS B
ORDER BY B.Name DESC


SET @SQL = 'SELECT id,
					Total,
					TotalPaid,
					Total - TotalPaid as UnPaid,
					LastCode
			FROM (	SELECT	id, 
							Total, 
							'+@Columns_paid+' as TotalPaid, 
							COALESCE(' + @Columns_code + ') as LastCode 
					FROM ##temp
			) x'
EXEC(@SQL);

It will now show the value of the last code column. If you want to show the column name than the query should be changed.

Kind regards,

Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".