How to Pivot and Unpivot Tables (rows to columns and column to rows)

CREATE TABLE STUDENT_MARKS(

Student_ID INT,

Student_Name VARCHAR(10),

Subject VARCHAR(10),

Marks INT)

INSERT INTO STUDENT_MARKS VALUES

(1,'A','English',75),(1,'A','Maths',88),(1,'A','Science',67),

(2,'B','English',88),(2,'B','Maths',82),(2,'B','Science',55),

(3,'C','English',65),(3,'C','Maths',50),(3,'C','Science',75),

(4,'D','English',76),(4,'D','Maths',100),(4,'D','Science',91)

SELECT Student_ID,Student_Name,Subject,Marks

FROM STUDENT_MARKS

Output:

Student_ID  Student_Name Subject    Marks

----------- ------------ ---------- -----------
1           A            English    75
1           A            Maths      88
1           A            Science    67
2           B            English    88

2           B            Maths      82

2           B            Science    55

3           C            English    65

3           C            Maths      50

3           C            Science    75

4           D            English    76

4           D            Maths      100

4           D            Science    91

--Pivot (convert subjects data into columns and show marks for respective subject for the students)

SELECT Student_ID,Student_Name,[English],[Maths],[Science]

FROM (SELECT Student_ID,Student_Name,Subject,Marks

      FROM STUDENT_MARKS)A

PIVOT (MAX(Marks) FOR SUBJECT IN ([English],[Maths],[Science]))AS PVT

Result:

Student_ID  Student_Name English     Maths       Science

----------- ------------ ----------- ----------- -----------
1           A            75          88          67
2           B            88          82          55
3           C            65          50          75

4           D            76          100         91

--Same result can be obtained without using PIVOT as mentioned below
SELECT Student_ID,Student_Name,

MAX(CASE WHEN Subject='English' THEN Marks ELSE 0 END)AS [English],

MAX(CASE WHEN Subject='Maths' THEN Marks ELSE 0 END)AS [Maths],

MAX(CASE WHEN Subject='Science' THEN Marks ELSE 0 END)AS [Science]

FROM STUDENT_MARKS

GROUP BY Student_ID,Student_Name

--Unpivot

--create a table from query shown above

SELECT Student_ID,Student_Name,[English],[Maths],[Science] INTO STUDENT_MARKS_PVT

FROM (SELECT Student_ID,Student_Name,Subject,Marks

   FROM STUDENT_MARKS)A

PIVOT (MAX(Marks) FOR SUBJECT IN ([English],[Maths],[Science]))AS PVT

SELECT Student_ID,Student_Name,[English],[Maths],[Science] FROM STUDENT_MARKS_PVT

Output:

Student_ID  Student_Name English     Maths       Science

----------- ------------ ----------- ----------- -----------
1           A            75          88          67
2           B            88          82          55
3           C            65          50          75

4           D            76          100         91

--Unpivot (convert subjects and marks into separate column for the students)

SELECT Student_ID,Student_Name,Subject,Marks

FROM (SELECT Student_ID,Student_Name,[English],[Maths],[Science]

   FROM STUDENT_MARKS_PVT)A

UNPIVOT (Marks FOR Subject IN ([English],[Maths],[Science]))AS UNPVT

Result:

Student_ID  Student_Name Subject   Marks

----------- ------------ --------- -----------
1           A            English   75
1           A            Maths     88
1           A            Science   67
2           B            English   88

2           B            Maths     82

2           B            Science   55

3           C            English   65

3           C            Maths     50

3           C            Science   75

4           D            English   76

4           D            Maths     100

4           D            Science   91

Search for Wildcards in a table

CREATE TABLE SEARCH_WILDCARD (ID INT,NAMES VARCHAR(25))

INSERT INTO SEARCH_WILDCARD VALUES

(1,'WILDCARD%WILDCARD'),

(2,'NO_WILDCARD'),

(3,'%'),

(4,'WILDCARD%'),

(5,'%WILDCARD')

SELECT * FROM SEARCH_WILDCARD

Output:

ID          NAMES

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

1           WILDCARD%WILDCARD

2           NO_WILDCARD

3           %

4           WILDCARD%

5           %WILDCARD

--Now if you want get records having '%'

--All values with % in any position

SELECT * FROM SEARCH_WILDCARD WHERE NAMES LIKE '%[%]%'

--All values only with %

SELECT * FROM SEARCH_WILDCARD WHERE NAMES LIKE '[%]' 

--All values ending with %

SELECT * FROM SEARCH_WILDCARD WHERE NAMES LIKE '%[%]' 

--All values starting with %

SELECT * FROM SEARCH_WILDCARD WHERE NAMES LIKE '[%]%'