I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: and I wanted to group by company_id to get something like: There is a built-in function in mySQL to do this group_concat Answer PostgreSQL 9.0 or later: Modern Postgres (since 2010) has the string_agg(expression, delimiter)
Tag: sql
SQL Server – Best way to get identity of inserted row?
What is the best way to get IDENTITY of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don’t understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? Answer @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
Database Design for Revisions?
We have a requirement in project to store all the revisions(Change History) for the entities in the database. Currently we have 2 designed proposals for this: e.g. for “Employee” Entity Design 1: –…
What is the syntax for an inner join in LINQ to SQL?
I’m writing a LINQ to SQL statement, and I’m after the standard syntax for a normal inner join with an ON clause in C#. How do you represent the following in LINQ to SQL: select DealerContact.* from …
How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
I didn’t see any similar questions asked on this topic, and I had to research this for something I’m working on right now. Thought I would post the answer for it in case anyone else had the same …
How to request a random row in SQL?
How can I request a random row (or as close to truly random as is possible) in pure SQL? Answer See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link): Select a random
Query a union table with fields as columns
I’m not quite sure if this is possible, or falls into the category of pivot tables, but I figured I’d go to the pros to see. I have three basic tables: Card, Property, and CardProperty. Since cards do not have the same properties, and often multiple values for the same property, I decided to use the union table approach to
Generate insert SQL statements from a CSV file
I need to import a csv file into Firebird and I’ve spent a couple of hours trying out some tools and none fit my needs. The main problem is that all the tools I’ve been trying like EMS Data Import and Firebird Data Wizard expect that my CSV file contains all the information needed by my Table. I need to
SQL Case Expression Syntax?
What is the complete and correct syntax for the SQL Case expression? Answer The complete syntax depends on the database engine you’re working with: For SQL Server: or: expressions, etc: Link: CASE (Transact-SQL) Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is
Decoding T-SQL CAST in C#/VB.NET
Recently our site has been deluged with the resurgence of the Asprox botnet SQL injection attack. Without going into details, the attack attempts to execute SQL code by encoding the T-SQL commands in …