Some handy SQL Clause and commands

Use Case

1. Using, For XML Path and STUFF to create something beautiful

+------+
| Name |
+------+
| C# |
| JS |
| TS |
| SQL |
| JSX |
+------+
C#,JS,TS,SQL,JSX
  1. Pivot the rows using For XML Path
  2. STUFF the result to remove unnecessary comma
SELECT ',' + Name FROM #tempTable FOR XML PATH ('')
,C#,JS,TS,SQL,JSX
STUFF ( character_expression , start , length , replaceWith_expression )
SELECT STUFF((SELECT ',' + Name FROM #tempTable FOR XML PATH ('')),1,1,'')
C#,JS,TS,SQL,JSX

2. Using Scary Pivot Table command

+-------+----------+
| DevId | Language |
+-------+----------+
| 1 | C# |
+-------+----------+
| 2 | C# |
+-------+----------+
| 3 | JS |
+-------+----------+
| 4 | JS |
+-------+----------+
| 5 | C# |
+-------+----------+
SELECT COUNT([language]) 'Total No of Devs', [language] FROM #tempTable GROUP BY [language]Result:+------------------+----------+
| Total No of Devs | Language |
+------------------+----------+
| 3 | C# |
+------------------+----------+
| 2 | JS |
+------------------+----------+
SELECT [C#],[JS]
FROM
(SELECT
[language] FROM #temp) AS sourceTable
PIVOT
(
COUNT
([language]) -- Aggregate function
FOR [language] IN ([C#],[JS]) -- Columns that we want to show
) AS pvtTable
+----+----+
| C# | JS |
+----+----+
| 3 | 2 |
+----+----+

3. Using Cross apply instead of inner join

Identity Table
+----+------+
| Id | Name |
+----+------+
| 1 | Jon |
+----+------+
| 2 | Jack |
+----+------+
| 3 | Jill |
+----+------+
Experience Table+-------+------------+----------+
| DevId | Experience | Language |
+-------+------------+----------+
| 1 | 3 | C# |
+-------+------------+----------+
| 1 | 2 | JS |
+-------+------------+----------+
| 1 | 4 | SQL |
+-------+------------+----------+
| 2 | 6 | C# |
+-------+------------+----------+
| 2 | 2 | JS |
+-------+------------+----------+
+----+------+-------+------------+----------+
| Id | Name | DevId | Experience | Language |
+----+------+-------+------------+----------+
| 1 | Jon | 1 | 4 | SQL |
+----+------+-------+------------+----------+
| 1 | Jon | 1 | 3 | C# |
+----+------+-------+------------+----------+
| 2 | JacK | 2 | 6 | C# |
+----+------+-------+------------+----------+
| 2 | JacK | 2 | 2 | JS |
+----+------+-------+------------+----------+
WITH ex AS
( SELECT #experience.*, ROW_NUMBER() OVER (PARTITION BY devId ORDER By experience DESC) AS rnk
FROM #experience
)
SELECT i.*, ex.*
FROM #identity i
INNER JOIN ex
ON
i.id = ex.devId AND ex.rnk <= 2
SELECT *
FROM #identity i
CROSS APPLY (SELECT TOP 2*
FROM #experience e
WHERE e.devId = i.id
ORDER BY e.experience DESC) ex

4. Curios case of Window function

+-------+------------+----------+
| DevId | Experience | Language |
+-------+------------+----------+
| 1 | 3 | C# |
+-------+------------+----------+
| 1 | 2 | JS |
+-------+------------+----------+
| 1 | 4 | SQL |
+-------+------------+----------+
| 2 | 6 | C# |
+-------+------------+----------+
| 2 | 2 | JS |
+-------+------------+----------+
+-------+------------+----------+
| DevId | Experience | Language |
+-------+------------+----------+
| 1 | 4 | SQL |
+-------+------------+----------+
| 2 | 6 | C# |
+-------+------------+----------+
WITH Tmp as(
SELECT e.* , ROW_NUMBER() OVER (PARTITION BY devId ORDER By experience DESC) AS rnk
FROM #experience e
)
SELECT t.devId, t.experience, t.language
FROM Tmp t
WHERE rnk <= 1

Conclusion

--

--

--

Developer | Learner | Batman Fan | Github: https://github.com/vikas0sharma | LinkedIn: https://www.linkedin.com/in/vikas-sharma-2849a7bb/

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Prometheus: A user’s guide in 10 minutes

Connect ODK Collect with any application using OpenFn.

Dreaming, memory consolidation and the Hard Drive parallel.

The 2021 RoadMap For Backend Developer

Automate AWS ECS Service Restarts using Lamda & EventBridge

Working with Mongo Atlas

How to add Navigation Drawer Menu in android| Todo Android app using Node JS and REST API | Part 4

10 Common ERP Mistakes

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vikas Sharma

Vikas Sharma

Developer | Learner | Batman Fan | Github: https://github.com/vikas0sharma | LinkedIn: https://www.linkedin.com/in/vikas-sharma-2849a7bb/

More from Medium

What is an index in SQL?

6 Major Differences Between SQL Server DELETE and TRUNCATE options.

INTRODUCTION TO SQL

Core SQL Commands