# SQL Tips and Tricks

> *Scenario when we need to filter specific select cases with joins*

```sql
SELECT DISTINCT ProgramID
,STUFF((
SELECT '|' + t1.TrancID
FROM @TBL_ReportColumns T1
WHERE T1.ProgramID = T2.ProgramID
FOR XML PATH('')
), 1, 1, '') AS TransactionCode
--,trancName,trancDesc
INTO #originalTransSlct
FROM @TBL_ReportColumns T2
ORDER BY ProgramID
```

> *Scenario when we need to convert rows from vertical to horizontal*

```sql
DECLARE @col_list VARCHAR(max) = ⠀ ''
DECLARE @a INT

SET @a = 0

SELECT @col_list = Stuff((
			SELECT ⠀ '
				,sn = max(CASE 
						WHEN sn = ⠀ ' + Cast(sn AS VARCHAR(50)) + ⠀ '
							THEN sn
						END)
				,DateRestrictionSettingName = max(CASE 
						WHEN sn = ⠀ ' + Cast(sn AS VARCHAR(50)) + ⠀ '
							THEN DateRestrictionSettingName
						END)
				,DateRestrictionSettingValue = max(CASE 
						WHEN sn = ⠀ ' + Cast(sn AS VARCHAR(50)) + ⠀ '
							THEN DateRestrictionSettingValue
						END) ⠀ '
			FROM #Table1
			ORDER BY sn
			FOR XML path('')
			), 1, 1, ⠀ '')

SET @col_list = ⠀ 'sn = max(CASE 
			WHEN sn = 1
				THEN sn
			END)
	,DateRestrictionSettingName1 = max(CASE 
			WHEN sn = 1
				THEN DateRestrictionSettingName
			END)
	,DateRestrictionSettingValue1 = max(CASE 
			WHEN sn = 1
				THEN DateRestrictionSettingValue
			END)
	,sn = max(CASE 
			WHEN sn = 2
				THEN sn
			END)
	,DateRestrictionSettingName2 = max(CASE 
			WHEN sn = 2
				THEN DateRestrictionSettingName
			END)
	,DateRestrictionSettingValue2 = max(CASE 
			WHEN sn = 2
				THEN DateRestrictionSettingValue
			END)
	,sn = max(CASE 
			WHEN sn = 3
				THEN sn
			END)
	,DateRestrictionSettingName3 = max(CASE 
			WHEN sn = 3
				THEN DateRestrictionSettingName
			END)
	,DateRestrictionSettingValue3 = max(CASE 
			WHEN sn = 3
				THEN DateRestrictionSettingValue
			END)
	,sn = max(CASE 
			WHEN sn = 4
				THEN sn
			END)
	,DateRestrictionSettingName4 = max(CASE 
			WHEN sn = 4
				THEN DateRestrictionSettingName
			END)
	,DateRestrictionSettingValue4 = max(CASE 
			WHEN sn = 4
				THEN DateRestrictionSettingValue
			END)
	,sn = max(CASE 
			WHEN sn = 5
				THEN sn
			END)
	,DateRestrictionSettingName5 = max(CASE 
			WHEN sn = 5
				THEN DateRestrictionSettingName
			END)
	,DateRestrictionSettingValue5 = max(CASE 
			WHEN sn = 5
				THEN DateRestrictionSettingValue
			END)
	,sn = max(CASE 
			WHEN sn = 6
				THEN sn
			END)
	,DateRestrictionSettingName6 = max(CASE 
			WHEN sn = 6
				THEN DateRestrictionSettingName
			END)
	,DateRestrictionSettingValue6 = max(CASE 
			WHEN sn = 6
				THEN DateRestrictionSettingValue
			END)
	,CONVERT(BIT, 1) AS editablerow
	,CONVERT(BIT, 0) AS deletablerow
	,CONVERT(BIT, 0) AS DateRestrictionSettingName6_editablecol
	,CONVERT(BIT, 0) AS DateRestrictionSettingValue6_editablecol⠀ '

EXEC (
		⠀ 'select ⠀ ' + @col_list + ⠀ ' FROM #Table1⠀ '
		)
```

> *Scenario when Date needs to be compared*

```sql
--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
SELECT DISTINCT SLCSemester.ID

	,ToCompare.StartDate
	,ToCompare.EndDate
	,CASE 
		WHEN ToCompare.StartDate >= SLCSemester.StartDate
			AND ToCompare.StartDate <= SLCSemester.EndDate
			AND ToCompare.EndDate >= SLCSemester.StartDate
			AND ToCompare.EndDate <= SLCSemester.EndDate
			THEN 'Engulfed'
		WHEN ToCompare.StartDate >= SLCSemester.StartDate
			AND ToCompare.StartDate <= SLCSemester.EndDate
			THEN 'SD'
		WHEN ToCompare.EndDate >= SLCSemester.StartDate
			AND ToCompare.EndDate <= SLCSemester.EndDate
			THEN 'ED'
		ELSE 'Something  wrong'
		END AS Issue
FROM SLCSemester
JOIN SLCSemester AS ToCompare ON ToCompare.ID = SLCSemester.ID ⠀ ” every row overlaps itself
	AND ToCompare.ID <> SLCSemester.ID
	OR (
		(
			ToCompare.StartDate >= SLCSemester.StartDate
			AND ToCompare.StartDate <= SLCSemester.EndDate
			)
		)
	OR (
		ToCompare.EndDate >= SLCSemester.StartDate
		AND ToCompare.EndDate <= SLCSemester.EndDate
		)
WHERE SLCSemester.SLCAcademicYearID = @SLCAcademicYearID )
```