Combine Multiple Rows into One Cell Using SQL
Vložit
- čas přidán 11. 09. 2024
- Learn to take values from multiple rows and combine them into one cell using the power of SQL. I'll demonstrate a very elegant function you can use to accommodate this use case. We'll work through several examples on a simple data set. As a data analyst you need this tip in your bag of tricks!
★☆★ FOLLOW ME BELOW: ★☆★
Blog ► anthonysmoak.com/
Twitter ► / anthonysmoak
Facebook ► / anthonybsmoak
Instagram ► / @anthonysmoakdata
Tableau Public ► bit.ly/3JMKsLY
#SQL #data #analytics #data #dataanalyst #education #onlinelearning
0:00: Intro
0:33 Explore data set in Excel
2:54 Explore data set in SQL Server
4:06 The function you came to learn
6:02 Combined data in alphabetical order
7:26 Microsoft documentation
8:02 Remove duplicates from results
9:40 Join results to initial data using a CTE
13:25 Outro (Remember to Super Thanks!)
You rock. SQL is so fun. I'm always learning something new. Thank you and thank everyone for sharing.
Thanks for the comment!
You rock for sharing this comment!
Thank you Anthony. I’m learning SQL, Tableau, and Python right now and your videos are so helpful!❤
Thank you for sharing this comment Jordan!
This was really a great help for me
Glad to hear it!
Great video. One suggestion... please consider either lowering or omitting the background music because I found it a bit distracting. Thank you, Anthony.
hello there, god bless your efforts..I have a simple enquiry as new sql learner.
How are CTEs different from temporary tables?
Thanks for taking care of this.
Great question. There are many differences but the biggest difference is that the CTE has to be used in the current query scope (re-execute the CTE code everytime you want to include those results). The temp table will exist for the entire duration of your session and does not need to be re-executed everytime you want to reference its results.
Is it work for oracle sql as well?
I believe Oracle has a string aggregate function. You may have to tweak the code a bit. Give it a try and let us know.
Will this work if i have it in views and there are alot of inner joins ?
The function should work against a view.
Do you perhaps know how to reverse this, i have a row with all the emails linked to the project in one cell separated by a ; i need to have all those emails listed each in a row and the project they dealt with
Try to experiment with the PARSENAME function in TSQL. The limitation may be that you need to know the project with the max number of emails, so you can repeat the PARSENAME function that many times. www.mssqltips.com/sqlservertip/6321/split-delimited-string-into-columns-in-sql-server-with-parsename/
@@AnthonySmoak thank you, I managed to find string_split
Ok great. I was thinking you needed each value as a new column instead of a row. String Split should work. 👍🏽