none
Pivot/UnPivot RRS feed

  • Question

  • Hi,

    It will be great if I can get an approach to get data in the desired structure.

    Please see attached info for source/target. Thanks 

     

    Monday, November 25, 2019 1:41 AM

Answers

  • Hi John_tay,

    Or please try following script.

    IF OBJECT_ID('Source') IS NOT NULL drop table  Source   
    go 
    Create Table Source
    (CollegeName varchar(15), 
    RatingMonth char(6), 
    Rating int, 
    Scoring int, 
    CollegeAdminStaffName varchar(10), 
    Comments varchar(20))
    Insert into Source Values
    ('ABC', 'Dec-18', 1, 5, 'Joy', 'Com ABC'),
    ('ABC', 'Nov-18', 4, 5, 'Andy', 'Com ABC2'),
    ('DEF', 'Dec-18', 2, 3, 'rich', 'Com DEF'),
    ('DEF', 'Nov-18', 3, 6, 'Sam', 'Com DEF2');
    
    ;with cte as(
    select CollegeName,RatingMonth,
    cast(Rating as varchar(20)) Rating, 
    cast(Scoring as varchar(20)) Scoring ,
    CollegeAdminStaffName,Comments
    from Source 
    )
    SELECT RatingMonth, [Column] KPI,
    case when PATINDEX('%[^0-9]%',Value)>0 then '' else Value end Value,
    CollegeAdminStaffName [Entered by],CollegeName College,
    case when PATINDEX('%[^0-9]%',Value)>0 then Value else '' end Commernts
    FROM cte t
    UNPIVOT ([Value] FOR [Column] IN ([Rating],[Scoring],[Comments])) u
    /*
    RatingMonth KPI           Value                Entered by College         Commernts
    ----------- ------------- -------------------- ---------- --------------- --------------------
    Dec-18      Rating        1                    Joy        ABC             
    Dec-18      Scoring       5                    Joy        ABC             
    Dec-18      Comments                           Joy        ABC             Com ABC
    Nov-18      Rating        4                    Andy       ABC             
    Nov-18      Scoring       5                    Andy       ABC             
    Nov-18      Comments                           Andy       ABC             Com ABC2
    Dec-18      Rating        2                    rich       DEF             
    Dec-18      Scoring       3                    rich       DEF             
    Dec-18      Comments                           rich       DEF             Com DEF
    Nov-18      Rating        3                    Sam        DEF             
    Nov-18      Scoring       6                    Sam        DEF             
    Nov-18      Comments                           Sam        DEF             Com DEF2
    
    */
    
    


    Best Regards,

    Rachel 


    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.

    • Marked as answer by John_tay Wednesday, January 15, 2020 1:03 AM
    Monday, November 25, 2019 7:13 AM
  • create table test
    (CollegeName varchar(50), 
    RatingMonth varchar(20), 
    Rating int, 
    Scoring int, 
    CollegeAdminStaffName varchar(10), 
    Comments varchar(500))
    Insert into test Values
    ('ABC','Dec-18',1,5,'Joy','Com ABC'),
    ('ABC','Nov-18',4,5,'Andy','Com ABC2'),
    ('DEF','Dec-18',2,3,'Rich','Com DEF'),
    ('DEF','Nov-18',3,6,'Sam','Com DEF2');
    
    select RatingMonth, KPI, value, CollegeAdminStaffName as entredby,CollegeName,'' as Comments 
    from test
    cross apply (values('rating',Rating),('scoring',Scoring)) d(KPI,Value)
    Union all
    select RatingMonth, 'Comment' as KPI, null as value, CollegeAdminStaffName as entredby,CollegeName,	 Comments 
    from test
    
    Order by CollegeName,RatingMonth,KPI desc
    
    
    drop table test

    • Marked as answer by John_tay Wednesday, January 15, 2020 1:03 AM
    Monday, November 25, 2019 3:18 PM
    Moderator

All replies

  • One way

    Create Table Foo(CollegeName varchar(15), RatingMonth char(6), Rating int, Scoring int, CollegeAdminStaffName varchar(10), Comments varchar(50));
    Insert Foo(CollegeName, RatingMonth, Rating, Scoring, CollegeAdminStaffName, Comments) Values
    ('ABC', 'Dec-18', 1, 5, 'Joy', 'Com ABC'),
    ('ABC', 'Nov-18', 4, 5, 'Andy', 'Com ABC2'),
    ('DEF', 'Dec-18', 2, 3, 'rich', 'Com DEF'),
    ('DEF', 'Nov-18', 3, 6, 'Sam', 'Com DEF2');
    
    Select f.RatingMonth As Month,
      Case n.Number When 1 Then 'rating' When 2 Then 'scoring' When 3 Then 'Comment' End As KPI,
      Case n.Number When 1 Then Rating  When 2 Then Scoring When 3 Then Null End As Value,
      f.CollegeAdminStaffName As EnteredBy, 
      f.CollegeName As College,
      Case n.Number When 1 Then Null  When 2 Then Null When 3 Then Comments End As Comments
    From Foo f
    Cross Join (Values(1),(2),(3)) As n(Number);
    

    Tom

    P.S. Thank you for the sample data and desired result.  That is very helpful.  But please do not give us the sample data in an image.  That makes it very hard for us to enter your sample data.  Best way to give us sample data is to use CREATE TABLE and INSERT statements like I did above.  Giving us the desired result as an image is fine.

    Monday, November 25, 2019 5:26 AM
  • Hi John_tay,

    Or please try following script.

    IF OBJECT_ID('Source') IS NOT NULL drop table  Source   
    go 
    Create Table Source
    (CollegeName varchar(15), 
    RatingMonth char(6), 
    Rating int, 
    Scoring int, 
    CollegeAdminStaffName varchar(10), 
    Comments varchar(20))
    Insert into Source Values
    ('ABC', 'Dec-18', 1, 5, 'Joy', 'Com ABC'),
    ('ABC', 'Nov-18', 4, 5, 'Andy', 'Com ABC2'),
    ('DEF', 'Dec-18', 2, 3, 'rich', 'Com DEF'),
    ('DEF', 'Nov-18', 3, 6, 'Sam', 'Com DEF2');
    
    ;with cte as(
    select CollegeName,RatingMonth,
    cast(Rating as varchar(20)) Rating, 
    cast(Scoring as varchar(20)) Scoring ,
    CollegeAdminStaffName,Comments
    from Source 
    )
    SELECT RatingMonth, [Column] KPI,
    case when PATINDEX('%[^0-9]%',Value)>0 then '' else Value end Value,
    CollegeAdminStaffName [Entered by],CollegeName College,
    case when PATINDEX('%[^0-9]%',Value)>0 then Value else '' end Commernts
    FROM cte t
    UNPIVOT ([Value] FOR [Column] IN ([Rating],[Scoring],[Comments])) u
    /*
    RatingMonth KPI           Value                Entered by College         Commernts
    ----------- ------------- -------------------- ---------- --------------- --------------------
    Dec-18      Rating        1                    Joy        ABC             
    Dec-18      Scoring       5                    Joy        ABC             
    Dec-18      Comments                           Joy        ABC             Com ABC
    Nov-18      Rating        4                    Andy       ABC             
    Nov-18      Scoring       5                    Andy       ABC             
    Nov-18      Comments                           Andy       ABC             Com ABC2
    Dec-18      Rating        2                    rich       DEF             
    Dec-18      Scoring       3                    rich       DEF             
    Dec-18      Comments                           rich       DEF             Com DEF
    Nov-18      Rating        3                    Sam        DEF             
    Nov-18      Scoring       6                    Sam        DEF             
    Nov-18      Comments                           Sam        DEF             Com DEF2
    
    */
    
    


    Best Regards,

    Rachel 


    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.

    • Marked as answer by John_tay Wednesday, January 15, 2020 1:03 AM
    Monday, November 25, 2019 7:13 AM
  • create table test
    (CollegeName varchar(50), 
    RatingMonth varchar(20), 
    Rating int, 
    Scoring int, 
    CollegeAdminStaffName varchar(10), 
    Comments varchar(500))
    Insert into test Values
    ('ABC','Dec-18',1,5,'Joy','Com ABC'),
    ('ABC','Nov-18',4,5,'Andy','Com ABC2'),
    ('DEF','Dec-18',2,3,'Rich','Com DEF'),
    ('DEF','Nov-18',3,6,'Sam','Com DEF2');
    
    select RatingMonth, KPI, value, CollegeAdminStaffName as entredby,CollegeName,'' as Comments 
    from test
    cross apply (values('rating',Rating),('scoring',Scoring)) d(KPI,Value)
    Union all
    select RatingMonth, 'Comment' as KPI, null as value, CollegeAdminStaffName as entredby,CollegeName,	 Comments 
    from test
    
    Order by CollegeName,RatingMonth,KPI desc
    
    
    drop table test

    • Marked as answer by John_tay Wednesday, January 15, 2020 1:03 AM
    Monday, November 25, 2019 3:18 PM
    Moderator