Order By in SQL Server with Condition or Clause

When you order by column in table in SQL Server but with the clause here is the example :

Suppose we have StateCity table.

 

 

 

Now we want to in a order that State NY is first and Else in Alphabetic Order here is the Query :

 

 

Select * from StateCity

Order By Case When [State]  = 'NY' Then '1'

              When [State]  = 'NJ' Then '2'

                       Else [State]  END ASC

 

Result:

 

 

 

Suppose you want to Order By Particular State and Particular City here is the example of the Query :

 

Select * from StateCity

Order By Case

                       When [State]  = 'NY' Then '1'

                       When [State]  = 'NJ' Then '2'

         

                                Else [State]  END ASC,

                       Case

                       When [City] = 'New York' Then '1'

                       When [City] = 'Atlanta' Then '1'

                       When [City] = 'Parsippany' Then '1'

 

                                 Else [City] END ASC

 

 

 Result:

 

 

 

 

 

 

 

 

 



 

SQL Query to Convert Multiple CSV Column to Row

Suppose you have following kind of table that has multiple CSV and you want to convert it to Row.





Want Result like :

 

First Create Following Table- Valued Functions in SQL Server

Create FUNCTION [dbo].[Splitstrings] (@List NVARCHAR(MAX),

@Delimiter NVARCHAR(255))

RETURNS @split_tab TABLE (

RN INT IDENTITY(1, 1),

SPLIT_VAL VARCHAR(100))

WITH SCHEMABINDING




AS
   BEGIN ;

;WITH E1(N)

AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a,E1 b),

E4(N) AS (SELECT 1 FROM E2 a, E2 b),

E42(N) AS (SELECT 1 FROM E4 a, E2 b),

cteTally(N)

AS (SELECT 0

UNION ALL

SELECT TOP (Datalength(Isnull(@List, 1))) Row_number()OVER (

ORDER BY (SELECT NULL))

FROM E42),

cteStart(N1)

AS (SELECT t.N + 1

FROM cteTally t

WHERE ( Substring(@List, t.N, 1) = @Delimiter

OR t.N = 0 ))

INSERT INTO @split_tab

SELECT SPLIT_VAL= Substring(@LIST, S.N1, Isnull(NULLIF(Charindex(@DELIMITER, @LIST, S.N1), 0) - S.N1, 8000))

FROM CTESTART S;

RETURN

END


Then Use Following Query (CTE) to Convert in to Row.



;WITH StudentSubjects

     AS
(
 
SELECT          StudentID,

                StudentName,

                Subjects,

                HoursPerWeek,

                Days,

                Time
FROM StudentClass 
 
),

SUBJECTS
  AS
(
SELECT    RN, -- RN FROM SPITSTRING FUNCTION

          StudentID,

          StudentName,

          Subjects=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Subjects, ',')),

 
 
HOURSPERWEEK
  AS
(
SELECT
          RN,

          StudentID,

          StudentName,

          HoursPerWeek=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (HoursPerWeek, ',')),

Days

AS
(
SELECT
          RN,

          StudentID,

          StudentName,

          Days=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Days, ',')),

Time

AS
(
 SELECT
           RN,

           StudentID,

           StudentName,

           Time=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Time, ','))

SELECT
           D.STUDENTID,

           D.STUDENTNAME,

           RTRIM(LTRIM(S.SUBJECTS)) AS SUBJECTS,

           RTRIM(LTRIM(H.HOURSPERWEEK)) AS HOURSPERWEEK,

           RTRIM(LTRIM(DA.DAYS)) AS DAYS,

           RTRIM(LTRIM(T.TIME)) AS TIME

 FROM StudentClass D

FULL OUTER JOIN SUBJECTS S

          ON D.StudentID = S.StudentID

FULL OUTER JOIN HOURSPERWEEK H

          ON D.StudentID = H.StudentID

          AND H.RN = S.RN

FULL OUTER JOIN DAYS DA

           ON D.StudentID = DA.StudentID

           AND DA.RN = COALESCE(S.RN, H.RN)

FULL OUTER JOIN TIME T

           ON D.StudentID = T.StudentID

           AND T.RN = COALESCE(S.RN, H.RN, DA.RN)



---------------------------------------------------------------------

SQL Create Table and Insert Value Script :

CREATE TABLE [dbo].[StudentClass](

[StudentID] [int] NULL,

[StudentName] [nvarchar](150) NULL,

[Subjects] [nvarchar](150) NULL,

[HoursPerWeek] [nvarchar](150) NULL,

[Days] [nvarchar](150) NULL,

[Time] [nvarchar](150) NULL

) ON [PRIMARY]




GO
  INSERT [dbo].[StudentClass] ([StudentID], [StudentName], [Subjects], [HoursPerWeek], [Days], [Time]) VALUES (1, N'Ria S', N'Maths, Science', N'10,15', N'Sun, Mon', N'10:20, 11:50')




GO
  INSERT [dbo].[StudentClass] ([StudentID], [StudentName], [Subjects], [HoursPerWeek], [Days], [Time]) VALUES (2, N'Robert T', N'Economics, Graphics, Accounting', N'15,18,16', N'Tue, Wed, Thur', N'10:60,13:20, 18::50')




GO