How do I split a string so I can access item x?
How do I split a string so I can access item x?
sql
What is SQL injection? [duplicate]
Can someone explain SQL injecton? SQL injection happens when you interpolate some content into a SQL query string, and the result modifies the syntax of your query in ways you didn’t intend. It doesn’t have to be malicious, it can be an accident. But accidental SQL injection is more likely to result in an error … Read more
Efficiently convert rows to columns in sql server
There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, … Read more
Get top 1 row of each group
I know this is an old thread but the TOP 1 WITH TIES solutions is quite nice and might be helpful to some reading through the solutions. select top 1 with ties DocumentID ,Status ,DateCreated from DocumentStatusLogs order by row_number() over (partition by DocumentID order by DateCreated desc) The select top 1 with ties clause … Read more
Parameterize an SQL IN clause
Parameterize an SQL IN clause
What is the difference between “INNER JOIN” and “OUTER JOIN”?
Assuming you’re joining on columns with no duplicates, which is a very common case: An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection. An outer join of A and B gives the results of A union B, i.e. the outer parts … Read more
Fetch the row which has the Max value for a column
Fetch the row which has the Max value for a column
How to concatenate text from multiple rows into a single text string in SQL Server
SQL Server 2017+ and SQL Azure: STRING_AGG Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery. STRING_AGG (Transact-SQL) Without grouping SELECT STRING_AGG(Name, ‘, ‘) AS Departments FROM HumanResources.Department; With grouping: SELECT GroupName, STRING_AGG(Name, ‘, ‘) AS Departments FROM HumanResources.Department GROUP … Read more
SQL Server dynamic PIVOT query?
Dynamic SQL PIVOT: create table temp ( date datetime, category varchar(3), amount money ) insert into temp values (‘1/1/2012’, ‘ABC’, 1000.00) insert into temp values (‘2/1/2012’, ‘DEF’, 500.00) insert into temp values (‘2/1/2012’, ‘GHI’, 800.00) insert into temp values (‘2/10/2012’, ‘DEF’, 700.00) insert into temp values (‘3/1/2012’, ‘ABC’, 1100.00) DECLARE @cols AS NVARCHAR(MAX), @query AS … Read more
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
SQL Server 2017 does introduce a new aggregate function STRING_AGG ( expression, separator). Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. The concatenated elements can be ordered by appending WITHIN GROUP (ORDER BY some_expression) For versions 2005-2016 I typically use the … Read more