Post reply

Getting Columns Names from rows and then replace with those column values

  • October 1, 2019 at 8:06 am

    #3684852

    Here is the scenario:

    A school send messages to students and message templates are saved with variables in angle brackets <>

    These variables needs to be replaced with real values and should be stored in ReadyToSendMessages. Below is complete table structure and expected output. Your help appreciated in advance.

    create table StudentMessages(Studentid int, StudentMessage varchar(8000),FineAmount money,AbsentDays int)

    INSERT INTO StudentMessages VALUES (1,'Your Fine amount is <FineAmount> and you were absent for <AbsentDays> days',10,4)

    INSERT INTO StudentMessages VALUES (2,'You will not be allowed to sit in exams as your absent days are <AbsentDays>',NULL,67)

    select * from StudentMessages

    CREATE TABLE ReadyToSendMessages(Studentid int, StudentMessage varchar(8000))

    --EXPECTED OUTPUT

    INSERT INTO ReadyToSendMessages values (1,'Your Fine amount is 10 and you were absent for 4 days')

    INSERT INTO ReadyToSendMessages values(2,'You will not be allowed to sit in exams as your absent days are 67')

    SELECT * FROM ReadyToSendMessages

     

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • October 1, 2019 at 8:19 am

    #3684861

    You can use something like below

    SELECT studentid   , 
    REPLACE(REPLACE(sm.StudentMessage , '<FineAmount>' , ISNULL(sm.FineAmount, '') ) ,
     '<AbsentDays>' , ISNULL(sm.AbsentDays , ''))
    FROM #StudentMessages AS sm

  • October 1, 2019 at 8:29 am

    #3684868

    I'm sure someone will have a better solution, but I can only see this being done with a cursor and dynamic sql

    you'll need a table mapping <fineamount> to whatever the source of the data is - lets call this "tagmap"

    so … something like

    --assume we have a variable with a record populated from student messages call @msg
    declare @strsql varchar(4000)
    declare @tag varchar(100)
    declare @field varchar(100)
    declare @val varchar(100)

    DECLARE CURS1 CURSOR FOR select tag,field from tagmap 
    open curs1
    fetch next from curs1 into @tag,@field
    while @@fetch_status=0
    begin
    set @strsql='select '+@field+' from whatevertable where student=''x'' '  
    exec sp_execute sql @strsql, @val output
    set @msg=replace(@msg,@tag,@val)
    --now insert into your final table
    fetch next from curs1 into @tag,@field
    end
    close curs1
    deallocate curs1

    select * from your final table

    apologies, there will be bits in there that are incorrect as i'm doing it from memory (and it is part pseudo code) - but it is a starting point - bonus is that if you have multiple tags in a message then all of them will be handled

    but like I said, someone might have a better solution

    MVDBA

Viewing 3 posts - 1 through 2 (of 2 total)

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