Gtjkyu

Dynamic SQL query to return results in columns I have this basic database table and am SQL beginner. +-------------+--------+----------+ | location | Week | Expenses | +-------------+--------+----------+ | Backoffice | 201851 | 80 | | frontoffice | 201852 | 110 | | Backoffice | 201901 | 120 | | Backoffice | 201902 | 70 | | frontoffice | 201903 | 68 | +-------------+--------+----------+ Is there a way to dynamically retrieve the last 5 weeks result, instead of hard coding it every time in my pivot table? Here is my code: SELECT * from (SELECT[week] ,[Expenses] from [cafe].[dbo].[table]where location = 'Backoffice' ) as Total_Expenses pivot (sum([expenses]) for [week] in ([201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909])) as pivotable; I would like to be able to just enter "5" and it shows me the last 5 week. May be by a "max" formula" what RDBMS system? SQ...

Replacing unwanted portions of a string value in SQL column I have a table in the following format where COL1 contains a unique identifier and COL2 contains a collection of phone numbers followed by a tag ( <abc> or <def> ) and delimited by pipe ( | ). Number of phone records in each row is unknown - it may contain just one phone number followed by tag or upto 10. <abc> <def> | Table ---------- COL1 : COL2 ---------- ID1 : 1234567890<abc>|4312314124<abc>|1232345133<def>|4131234131<abc>|41234134132<def> I need to copy this data into a new table with the result in following format i.e. remove all portion of the string with the tag <def> . <def> Table ---------- COL1 : COL2 ---------- ID1 : 1234567890<abc>,4312314124<abc>,4131234131<abc> What are the best ways to do this to get optimum performance? I need the program to transform data in a table that contains about a million records. ...