Let’s say we have a table in which we store the various test results of a persons.
And assuming that it has been queried and stored in a list of objects.
results= db.Query<PersonResult>(SELECT * FROM Results).ToList();
(This is a Dapper query)
To get the result of one person in one row, we can do a self-join with LINQ as following:
var modifiedList = from r1 in theList.Where(x => x.TestId == "IELTS").ToList()
join r2 in theList.Where(x => x.TestId == "TOEFL").ToList()
r1.PersonId equals r2.PersonId
select new Person
TestId = r1.TestId,
ResultIelts = r1.Resul
Or another way, I think better is to do is with SQL in the server:
t1.PersonId, t1.Result as TOEFLResult, t2.Items AS IELTSResult
FROM Results t1, Results t2
WHERE t1.PersonId= t2.PersonId
AND t1.TestId = 'TOEFL' and t2.TestID='IELTS'
ORDER BY t1.CompletionDate DESC
#linq #sql #self-join #combine-two-rows-sql #merge-two-row-sql-linq #merge-rows
There are some cases that you can not change your code if you have an ancient project. If it causes to insert wrong values to db, you may want to validate the value is ok and if not, correct it. We can use db trigger for such cases.
CREATE TRIGGER [dbo].[triggerName]
DECLARE @Name AS varchar(5000)
DECLARE @Category AS varchar(50)
DECLARE @ID AS int
SET @FirstName=(select TestID from inserted)
SET @ID = (select ResultID from inserted)
IF @Category = 'CARS'
SET @Name=(select name from inserted)
IF LEN(@Name) != 50 -- Our condition is the length of the name must be 50 if not, that means it has 0s and we need to replace 0s.
-- 'Lentgth is incorrect:' + Cast(LEN(@Name) as varchar(50))
IF CHARINDEX('0',@Name)>0 -- If name contains any 0s, update those 0s with 'X' - here we update the table record
PRINT 'Includes Zero'
SET Items = Replace(@Name,'0','X')
WHERE ID = @ID
-- 'IT IS NOT CATEGORY = CARS
Important thing here is the type of trigger FOR INSERT
This type runs after each insert operation.
Learn more here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
Multiple arguments in SQL IF:
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
SET @StartDate = NULL
SET @EndDate = NULL
IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL)
Select 'This works just fine' as Msg
Select 'No Lol' as Msg
#sql #mssql #db-trigger, #insert-trigger, #sql-trigger