ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Generate Matrix table from Database using PIVOT function in SQL Server<p>Hey <a class="username" rel="abualmazen">abualmazen</a>,</p> <p>Please refer below query sample.</p> <p><span style="text-decoration: underline;"><strong>SQL </strong></span></p> <pre class="brush: sql">CREATE TABLE #teachers(teacher_id INT, teacher_name VARCHAR(10)) INSERT INTO #teachers VALUES(1,&#39;ali&#39;) INSERT INTO #teachers VALUES(2,&#39;ahmed&#39;) INSERT INTO #teachers VALUES(3,&#39;salim&#39;) INSERT INTO #teachers VALUES(4,&#39;obaid&#39;) CREATE TABLE #schools(school_id INT, school_name VARCHAR(10)) INSERT INTO #schools VALUES(1,&#39;school1&#39;) INSERT INTO #schools VALUES(2,&#39;school2&#39;) INSERT INTO #schools VALUES(3,&#39;school3&#39;) INSERT INTO #schools VALUES(4,&#39;school4&#39;) CREATE TABLE #visiting_time(id INT, dat VARCHAR(50),teacher_id INT, school_id INT) INSERT INTO #visiting_time VALUES(1,&#39;9/12/2018&#39;,1,1) INSERT INTO #visiting_time VALUES(2,&#39;10/12/2018&#39;,1,2) INSERT INTO #visiting_time VALUES(3,&#39;11/12/2018&#39;,2,3) INSERT INTO #visiting_time VALUES(4,&#39;12/12/2018&#39;,3,4) INSERT INTO #visiting_time VALUES(5,&#39;13/12/2018&#39;,1,3) INSERT INTO #visiting_time VALUES(6,&#39;16/12/2018&#39;, 2,4) INSERT INTO #visiting_time VALUES(7,&#39;17/12/2018&#39;,3,1) INSERT INTO #visiting_time VALUES(8,&#39;18/12/2018&#39;,3,2) INSERT INTO #visiting_time VALUES(9,&#39;19/12/2018&#39;, 2,1) INSERT INTO #visiting_time VALUES(10,&#39;20/12/2018&#39;,1,4) DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName = ISNULL(@ColumnName + &#39;,&#39;,&#39;&#39;)+ QUOTENAME(dat) FROM (SELECT DISTINCT [dat] FROM #visiting_time) AS [dat] SET @DynamicPivotQuery = &#39;;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,&#39;+@ColumnName+&#39; FROM CTE PIVOT (MAX(school_name) FOR [dat] IN(&#39;+@ColumnName+&#39;)) p ORDER BY teacher_name ASC&#39; EXEC(@DynamicPivotQuery) DROP TABLE #teachers DROP TABLE #schools DROP TABLE #visiting_time</pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/152905/Generate-Matrix-table-from-Database-using-PIVOT-function-in-SQL-Server/https://www.aspforums.net:443/Threads/152905/Generate-Matrix-table-from-Database-using-PIVOT-function-in-SQL-Server/Mon, 10 Dec 2018 02:40:37 GMT