locked
Convert Rows to Column In SQL Server RRS feed

  • Question

  • Dears,

    I want to Use Pivot to convert Rows to Column

    I have three Tables

    1) Student Table with Column (StudentID and StudentName)

    2) Subject Table With Column (SubjectID and SubjectName)

    3) Student Subject Table With Column (StudentSubjectID , StudentID , SubjectID and Date)

    Now i made a query to Get Data from the above Tables

    StudentID   StudentName   SubjectID   SubjectName  DateTime  

    1                  Yasser                 1               Math               1/1/2017

    1                  Yasser                 1               English             1/1/2017

    1                  Yasser                 1               Math               3/1/2017

    1                  Mark                   1               Math               1/1/2017

    1                 John                    1               Math               6/1/2017

    Now i will make a monthly report to display Student Subject per month and output will be

    Student/Days    1/1/2017      2/1/2017     3/1/2017    4/1/2017  ......................................... 30/1/2017    (All days for month)

    Yasser                 Math             -                 Math           -                                                -

                               English             -               -                 -                                                -

    Mark                   Math             -                 -                  -                                                -

    How can i do this

    Thank you

    Tuesday, January 3, 2017 6:30 PM

All replies

  • You may want to look over this posting about dynamic pivots. For a basic pivot you need to specify each value and that may be very difficult over time to maintain.

    http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

    EDIT: I suggest you use SSRS to create your report. The issue you're going to run into is that PIVOT requires an aggregate. So you'd be getting max or min and you wouldn't ever end up with your report example results. The results you posted are very simple to do in a reporting tool such as SSRS.

    Example of your road block:

    create table student (Id int, Name varchar(20))
    insert into student (id, name) values (1, 'John'), (2, 'Mark')
    
    create table subject (Id int, Name varchar(20))
    insert into subject (id, name) values (1, 'Math'), (2, 'Science'), (3, 'English')
    
    create table studentsubject (StudentId int, SubjectId int, DateEnrolled date)
    insert into studentsubject (StudentId , SubjectId, DateEnrolled) values (1, 1, '2016-11-01'), (1, 2, '2016-12-01'), (2, 1, '2016-11-01'), (2, 2, '2016-11-01'), (1, 3, '2016-12-01')
    
    
    select *
    from (	select s.Name as Student, j.name as Course, ss.dateenrolled 
    		from student s
    		inner join studentsubject ss on (s.id = ss.studentid)
    		inner join subject j on (j.id = ss.subjectid) ) as source
    PIVOT (max(Course) FOR dateenrolled IN ([2016-11-01], [2016-12-01])) as Pvt

    Note that the MAX is used to satisfy the pivot syntax. You could also use MIN or any other aggregate function but this won't work for the results you need.

    Instead of using PIVOT, use SSRS.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    • Edited by Daniel Janik Tuesday, January 3, 2017 7:30 PM added workaround
    • Proposed as answer by Naomi N Tuesday, January 3, 2017 8:21 PM
    Tuesday, January 3, 2017 7:14 PM
  • Post the scripts to create three tables and to insert data.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, January 3, 2017 7:19 PM
  • Thank you but i want to display all course not max how can i do this?

    Wednesday, January 4, 2017 4:31 PM
  • Hi Yasser Zaid,

    >> i want to display all course not max how can i do this?

    You could have a look at this following code that first combines the courses into one on a same day and then convert rows into columns. It just contains 2017-01-01 to 2017-01-07 to show how to use the pivot and show all courses, if you want to show monthly report, you’d better achieve it SSRS as suggested above.

    declare @t table(StudentID int,   StudentName varchar(100),   SubjectID int,   SubjectName varchar(100),  DateTime date)
    insert into @t values
    (1,'Yasser',1,'Math','2017-01-01'),
    (1,'Yasser',1,'English','2017-01-01'),
    (1,'Yasser',1,'Math','2017-01-03'),
    (1,'Mark',1,'Math','2017-01-01'),
    (1,'John',1,'Math','2017-01-06')
    
    select * from @t 
    
    ;WITH dates AS 
    (
         SELECT CAST('2017-01-01' AS date) date
         UNION ALL
    
         SELECT DATEADD(dd, 1, t.date)
         FROM dates t
         WHERE DATEADD(dd, 1, t.date) < '2017-02-01'
    )
    ,cte as
    (
    	select StudentName,[DateTime],
    	(
    		STUFF(
    				(
    					select ','+SubjectName from @t t2 
    					where t1.StudentName = t2.StudentName and t1.DateTime = t2.DateTime 
    					for xml path('')
    				),1,1,'')
    	) as SubjectNames
    	from @t t1
    	group by StudentName,[DateTime]
    )
    select *
    from 
    (
    	select d.date as [date], COALESCE (c.StudentName,d.StudentName) as StudentName,c.SubjectNames as SubjectNames
    	from 
    	(
    		select dd.date,tt.StudentName from dates dd 
    		cross join (select distinct StudentName from @t) tt
    	) d
    	left join cte c on d.date = c.DateTime 
    ) s
    PIVOT (max(s.SubjectNames) FOR s.date IN ([2017-01-01], [2017-01-02], [2017-01-03], [2017-01-04], [2017-01-05], [2017-01-06], [2017-01-07])) as P

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 5, 2017 7:32 AM
  • Dear

    Thank you but if i want to Pass DateTime to get all month date how can i do this

    Also for Student "Yasser" if i want to display Subject in different rows without ','

    Thank you.

    Thursday, January 5, 2017 9:58 PM
  • Hi Yasser Zaid,

    >> if i want to Pass DateTime to get all month date how can i do this

    If you want to pass a parameter to get all month date, achieving it in T-SQL would be very tedious and meaningless. I strongly you’d better consider showing the report in SSRS which achieving it would be much easier than T-SQL coding. This link provides a demo to explain this situation and you could refer to. For further issues about SSRS programming, it’s better to post them in SSRS forum.

    >> Also for Student "Yasser" if i want to display Subject in different rows without ','

    In T-SQL, you could have a look at this article to learn how to split a string into multiple lines and then use cross apply to achieve your goal.

    In SSRS, you could have a look at this link. You would found that it would be easier than in T-SQL to achieve the break line in one column.

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Albert_ Zhang Wednesday, February 8, 2017 1:16 PM
    Friday, January 6, 2017 10:59 AM
  • DECLARE @Student TABLE (
    	StudentID INT, 
    	StudentName VARCHAR(50)
    );
    DECLARE @Subject TABLE (
    	SubjectID INT, 
    	SubjectName VARCHAR(50)
    );
    DECLARE @StudentSubject TABLE (
    	StudentSubjectID INT IDENTITY(1, 1), 
    	StudentID INT, 
    	SubjectID INT,
    	DateEnrolled date
    );
    DECLARE @StudentClass TABLE (
    	StudentName VARCHAR(50),
    	SubjectName VARCHAR(50),
    	DateEnrolled date
    );
    
    INSERT INTO @Student(StudentID, StudentName)
    VALUES (1, 'Yasser'), (2, 'Mark'), (3, 'John');
    
    INSERT INTO @Subject(SubjectID, SubjectName)
    VALUES (1, 'Math'), (2, 'English'), (3, 'Chemistry');
    
    SET DATEFORMAT dmy
    INSERT INTO @StudentSubject(StudentID, SubjectID, DateEnrolled)
    VALUES(1, 1, '1/1/2017'), (1, 2, '1/1/2017'), (1, 3, '3/1/2017'), (2, 1, '1/1/2017'), (3, 1, '6/1/2017');
    
    INSERT INTO @StudentClass
    SELECT s.StudentName, c.SubjectName, ss.DateEnrolled
    FROM @Student AS s
    INNER JOIN @StudentSubject AS ss ON ss.StudentID = s.StudentID
    INNER JOIN @Subject AS c ON c.SubjectID = ss.SubjectID
    ORDER BY s.StudentID;
    
    SELECT 
    	 StudentName
    	,CASE WHEN DateEnrolled = '2017-01-01' THEN SubjectName ELSE '-' END AS '2017-01-01'
    	,CASE WHEN DateEnrolled = '2017-01-02' THEN SubjectName ELSE '-' END AS '2017-01-02'
    	,CASE WHEN DateEnrolled = '2017-01-03' THEN SubjectName ELSE '-' END AS '2017-01-03'
    	,CASE WHEN DateEnrolled = '2017-01-04' THEN SubjectName ELSE '-' END AS '2017-01-04'
    	,CASE WHEN DateEnrolled = '2017-01-05' THEN SubjectName ELSE '-' END AS '2017-01-05'
    	,CASE WHEN DateEnrolled = '2017-01-06' THEN SubjectName ELSE '-' END AS '2017-01-06'
    	--,......
    	,CASE WHEN DateEnrolled = '2017-01-31' THEN SubjectName ELSE '-' END AS '2017-01-31'
    FROM @StudentClass


    A Fan of SSIS, SSRS and SSAS

    Friday, January 6, 2017 9:27 PM