Cannot Connect to Server – A network-related or instance-specific error
Cannot Connect to Server – A network-related or instance-specific error
sql-server
How can I remove duplicate rows?
How can I remove duplicate rows?
Convert Rows to columns using ‘Pivot’ in SQL Server
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns. It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially. First up, here are … Read more
How can you represent inheritance in a database?
@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview: Single Table Inheritance (aka Table Per Hierarchy Inheritance): Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will … Read more
Why do we always prefer using parameters in SQL statements?
Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a program interface such as a desktop program or web site. In your example, a user can directly run SQL code on your database by crafting statements in txtSalary. For example, if they were to write 0 OR 1=1, the … Read more
How do I split a string so I can access item x?
How do I split a string so I can access item x?
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
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