Dynamic column result

kasim mohamed 581 Reputation points
2023-09-14T06:58:29.2+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,612 questions
0 comments No comments
{count} votes

Accepted answer
  1. Wilko van de Velde 2,146 Reputation points
    2023-09-14T07:42:42.4133333+00:00

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


0 additional answers

Sort by: Most helpful