CODEWITHSUNDEEP
sql-servert-sql
H22
H22

Reputation: 169

SQL Putting parts of column name(s) into another column

I have Result Set that gets populated in a stored procedure and can have dynamic column names.

ex.

create table #ResultSet_fields
(
    claimId int,
    adjustmentVersion int,
    ServiceDateFrom date,
    ServiceDateTo date,
    ProcedureCode varchar(10),
    PlaceOfService varchar(3),
    ChangedField varchar(max)
)

At runtime the #ResultSet can have columns added which end in suffix _chg, for example ProcedureCode_chg and PlaceOfService_chg.

I need to populate ChangedField in the result set as ProcedureCode|PlaceOfService depending on if the columns ProcedureCode_chg and PlaceOfService_chg exist and if they're populated with data.

I won't be able to reference the columns directly because I won't know what they are until runtime.

Can someone steer me in the right direction to do this?

Upvotes: 0

Views: 755

Answers (3)

Gudwlk
Gudwlk

Reputation: 1157

I used dynamic approach to prepare the update statement. then you will not hve a problem with the column name. I added ProcedureCode_chg to check the result. You can make this statement as an alter table statement just after your create statement.

create table #ResultSet_fields(
 claimId int,
 adjustmentVersion int,
 ServiceDateFrom date,
 ServiceDateTo date,
 ProcedureCode varchar(10),
 PlaceOfService varchar(3),
 ChangedField varchar(max),
 test VARCHAR(MAX),
 ProcedureCode_chg varchar(max),
 PlaceOfService_chg varchar(max), --additional columns 
 test_chg varchar(max) -- Additional columns
 )
--test with the below insert
 insert into #ResultSet_fields values (1,1,'2021-08-01','2021-08-01','100', 'A', 'ss', 'test', null,'jj',null)

  IF EXISTS (  SELECT 1  FROM tempDb.sys.columns WHERE [object_ID] = 
      OBJECT_ID('tempDb..#ResultSet_fields')
        AND [name] LIKE '%__chg' )

BEGIN

    DECLARE @ColumnName AS NVARCHAR(MAX), @SQL AS NVARCHAR(MAX), @count_ int   

        SELECT ROW_NUMBER() OVER (ORDER BY name) AS id, name  
        INTO #Temp_suffixedColumns
        FROM tempDb.sys.columns WHERE [object_ID] = OBJECT_ID('tempDb..#ResultSet_fields')
        AND [name] LIKE '%__chg'

        DECLARE @count_SuffixedColumns  INT , @counter INT =1 
        SELECT @count_SuffixedColumns = COUNT(1) FROM #Temp_suffixedColumns
 
        WHILE (@counter <= @count_SuffixedColumns)
            BEGIN
 
                SELECT @ColumnName = [name]  
                FROM tempDb.sys.columns 
                WHERE [object_ID] = OBJECT_ID('tempDb..#ResultSet_fields')
                        AND [name] IN ( SELECT  SUBSTRING(name,0,CHARINDEX('_chg',name, 0) ) 
                                        FROM #Temp_suffixedColumns 
                                        WHERE   [name]    LIKE '%__chg' AND id = @counter)
    
     
                 SET @SQL =   'Update #ResultSet_fields SET ChangedField =  '''+  @ColumnName +''' 
                    FROM #ResultSet_fields where (' +@ColumnName+'_chg is not null or ' +@ColumnName+'_chg <> '''')'
    
                EXEC sp_executesql @SQL 
                SET @counter = @counter +1

                --SELECT @SQL

            END   
    
END

  --to check the update is correct
   SELECT * FROM #ResultSet_fields
   DROP TABLE #ResultSet_fields 
   DROP TABLE #Temp_suffixedColumns

Upvotes: 1

dbunk
dbunk

Reputation: 16

Check system tables in tempdb for if column names like "%_Chn" exist at runtime and if so you should be able to continue processing for the column(s) that exist with change suffix and then do more logic on evaluating what exists in those columns.. trick will be that is you need to dynamically build/execute sql statements you could do that within calling program language or if want to do that right on DBMS you might be able to build sqlstring dynamically then execute that string (you'd need to create set variables and string together sqlstatement based on what is in tempdb columns name returned by below...

create table #ResultSet_fields
(
 PlaceOfService varchar(3),
 PlaceOfService_Chg varchar(3),
 ColumnX varchar(3),
 ColumnX_Chg varchar(3),
 ColumnY varchar(3)
)
 
select 
name as ChangeField,
substring(name,1,charindex('_Chg',name)-1) as SisterField
from tempdb.sys.columns 
where object_id = object_id('tempdb..#ResultSet_fields')
and name like '%_Chg'

Upvotes: 0

nbk
nbk

Reputation: 49373

You can check if the columns already exists and also chekc if theay are not null#

create table #ResultSet_fields(
 claimId int,
 adjustmentVersion int,
 ServiceDateFrom date,
 ServiceDateTo date,
 ProcedureCode varchar(10),
 PlaceOfService varchar(3),
 ChangedField varchar(max)
 )

IF COL_LENGTH('#ResultSet_fields', 'ProcedureCode_chg') IS NOT NULL ANd COL_LENGTH('#ResultSet_fields', 'ProcedureCode_chg') IS NOT NULL
BEGIN
    UPDATE #ResultSet_fields SET ChangedField = ProcedureCode + PlaceOfService;
END
GO

db<>fiddle here

Upvotes: 0

Related Questions