Generate Matrix table from Database using PIVOT function in SQL Server

Last Reply one month ago By pandeyism

Posted one month ago

Hi

How to select data from sql as matrix.

i have 3 tables

teachers

teacher_id teacher_name
1 ali
2 ahmed
3 salim
4 obaid

schools

school_id school_name
1 school1
2 school2
3 school3
4 school4

visiting_time

id dat teacher_id school_id
1 09/12/2018 1 1
2 10/12/2018 1 2
3 11/12/2018 2 3
4 12/12/2018 3 4
5 13/12/2018 1 3
6 16/12/2018 2 4
7 17/12/2018 3 1
8 18/12/2018 3 2
9 19/12/2018 2 1
10 20/12/2018 1 4

how i can select data as matrix like

result

  09/12/2018 10/12/2018 11/12/2018 12/12/2018 13/12/2018 14/12/2018 16/12/2018 17/12/2018 18/12/2018 19/12/2018 20/12/2018
ali school1 school2     school3           school4
ahmed     school3       school4     school1  
salim       school4       school1 school2    
obaid                      

Thank you

You are viewing reply posted by: pandeyism one month ago.
Posted one month ago

Hey abualmazen,

Please refer below query sample.

SQL

CREATE TABLE #teachers(teacher_id INT, teacher_name VARCHAR(10))
INSERT INTO #teachers VALUES(1,'ali')
INSERT INTO #teachers VALUES(2,'ahmed')
INSERT INTO #teachers VALUES(3,'salim')
INSERT INTO #teachers VALUES(4,'obaid')

CREATE TABLE #schools(school_id INT, school_name VARCHAR(10))
INSERT INTO #schools VALUES(1,'school1')
INSERT INTO #schools VALUES(2,'school2')
INSERT INTO #schools VALUES(3,'school3')
INSERT INTO #schools VALUES(4,'school4')

CREATE TABLE #visiting_time(id INT, dat VARCHAR(50),teacher_id INT, school_id INT)
INSERT INTO #visiting_time VALUES(1,'9/12/2018',1,1)
INSERT INTO #visiting_time VALUES(2,'10/12/2018',1,2)
INSERT INTO #visiting_time VALUES(3,'11/12/2018',2,3)
INSERT INTO #visiting_time VALUES(4,'12/12/2018',3,4)
INSERT INTO #visiting_time VALUES(5,'13/12/2018',1,3)
INSERT INTO #visiting_time VALUES(6,'16/12/2018',	2,4)
INSERT INTO #visiting_time VALUES(7,'17/12/2018',3,1)
INSERT INTO #visiting_time VALUES(8,'18/12/2018',3,2)
INSERT INTO #visiting_time VALUES(9,'19/12/2018',	2,1)
INSERT INTO #visiting_time VALUES(10,'20/12/2018',1,4)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(dat) FROM (SELECT DISTINCT [dat] FROM #visiting_time) AS [dat]

SET @DynamicPivotQuery = ';WITH CTE AS(
SELECT v.dat,t.teacher_name,s.school_name FROM #visiting_time v
INNER JOIN #schools s ON s.school_id = v.school_id
INNER JOIN #teachers t ON t.teacher_id = v.teacher_id
)
SELECT teacher_name,'+@ColumnName+' FROM CTE
PIVOT (MAX(school_name) FOR [dat] IN('+@ColumnName+')) p
ORDER BY teacher_name ASC'
EXEC(@DynamicPivotQuery)

DROP TABLE #teachers
DROP TABLE #schools
DROP TABLE #visiting_time