Say I have a table named "ONE" with a single column "Column1." Let the data be as below:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
Column1 ------- a b c
What will be the query to display the three records concatenated into a single row? I was unable to answer this question in an interview!! Thanks in advance.
What kind of a question is that?? The interviewer was clearly trying to rattle you, because that's a pretty dumb question. I would have been tempted to reply "The answer is available by inspection -- 'a,b,c'. Voila and QED. I don't write queries for trivialities. Now please ask me something serious, like the difference between surrogate and natural keys."
Okay, let's look at some solutions. Here's the answer in MySQL:
select group_concat(Column1) from ONE
Here's the answer in Sybase ASA:
select list(Column1) from ONE
If you're using any other database system, it gets a little harder. In Oracle, you can try some of these methods. Other databases require similar approaches.
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.