Rohit Varma's blog

SQL Tips and Tricks

Rohit Varma's photo
Rohit Varma
·

3 min read

Scenario when we need to filter specific select cases with joins

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

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

--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 )
SQL