Generate Matrix table from Database using PIVOT function in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months 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

Posted 3 months 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