question

b.joseph avatar image
b.joseph asked

How to do Pivoting in SQL Server without calculation

Hi, I want to pivot a table, I tried self-join but it is very slow. kindly help me to do this.Below is my table structure.
STUDENT_ID   AC_YEAR    AC_TERM     STANDING      ENRL_TERM_SEQ 
10001         2015       SPRING        NULL           1          
10001         2015       FALL          NULL           2          
10001         2016       SPRING         W             3          
10001         2016       FALL           W             4          
10002         2015       SPRING        NULL           1          
10002         2015       FALL           W             2          
10002         2016       SPRING        NULL           3          
10002         2016       FALL          NULL           4          
10005         2015       SPRING        NULL           1          
10005         2015       FALL          NULL           2         
10005         2016       SPRING        NULL           3          
10005         2016       SUMMER        NULL           4          
10005         2016       FALL          NULL           5  
I want the output as below
ST_ID  CUR_YR  CUR_TM CUR_SD  PR1_YR  PR1_TM  PR1_SD  PR2_YR PR_TM  PR2_SD 
10001  2015    SPRING   NULL   NULL   NULL    NULL    NULL   NULL    NULL
10001  2015    FALL     NULL   2015   SPRING  NULL    NULL   NULL    NULL
10001  2016    SPRING    W     2015   FALL    NULL    2015   SPRING  NULL
10001  2016    FALL      W     2016   SPRING   W      2015   FALL    NULL
10002  2015    SPRING   NULL   NULL   NULL    NULL    NULL   NULL    NULL
10002  2015    FALL      W     2015   SPRING  NULL    NULL   NULL    NULL
10002  2016    SPRING   NULL   2015   FALL     W      2015   SPRING  NULL
10002  2016    FALL     NULL   2016   SPRING  NULL    2015   FALL     W
10005  2015    SPRING   NULL   NULL   NULL    NULL    NULL   NULL    NULL
10005  2015    FALL     NULL   2015   SPRING  NULL    NULL   NULL    NULL
10005  2016    SPRING   NULL   2015   FALL    NULL    2015   SPRING  NULL
10005  2016    SUMMER   NULL   2016   SPRING  NULL    2015   FALL    NULL
10005  2016    FALL     NULL   2016   SUMMER  NULL    2016   SPRING  NULL
Kindly let me know how can i do this,i have tried self-joins but that is very slow Thanks in advance
sql server 2012
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I guess your question is a bit misleading :)
1 Like 1 ·
Sounds like you want a cross-tab - try using the technique in http://www.sqlservercentral.com/articles/T-SQL/63681/
0 Likes 0 ·
Thank you, let me check it
0 Likes 0 ·
It is not a simple pivoting,you can see,it keeps the previous terms value also
0 Likes 0 ·
Hi Usman, Sorry for that.I want to transpose or Pivot the data by keeping previous terms data. Simple Pivot will do like this
ST_ID  CUR_YR  CUR_TM CUR_SD  PR1_YR  PR1_TM  PR1_SD  PR2_YR PR_TM  PR2_SD
10001  2016    FALL    W      2016    SPRING    W     2015    FALL   NULL
10002  2016    FALL   NULL    2016    SPRING   NULL   2015    FALL    W 
10005  2016    FALL   NULL    2016    SUMMER   NULL   2016   SPRING  NULL
You can see one row for each students , but i need to get that same number of records while pivoting and each row will take previous term's data Hope it is clear Thank you so much
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
I believe all you need is to use [LAG][1] function. Something like the following select a.StdID ST_ID, a.Year CUR_YR, a.AC_TERM CUR_TM, a.AC_STANDING CUR_SD ,LAG(a.Year, 1, null) over(partition by StdId order by term_seq) PR1_YR ,LAG(a.AC_TERM, 1, null) over(partition by StdId order by term_seq) PR1_TM ,LAG(a.AC_STANDING, 1, null) over(partition by StdId order by term_seq) PR1_SD ,LAG(a.Year, 2, null) over(partition by StdId order by term_seq) PR2_YR ,LAG(a.AC_TERM, 2, null) over(partition by StdId order by term_seq) PR_TM ,LAG(a.AC_STANDING, 2, null) over(partition by StdId order by term_seq) PR2_SD from ( SELECT 10001 StdID, 2015 Year, 'SPRING' AC_TERM, NULL AC_STANDING, 1 Term_Seq union all SELECT 10001 StdID, 2015 Year, 'FALL' AC_TERM, NULL AC_STANDING, 2 Term_Seq union all SELECT 10001 StdID, 2016 Year, 'SPRING' AC_TERM, 'W' AC_STANDING, 3 Term_Seq union all SELECT 10001 StdID, 2016 Year, 'FALL' AC_TERM, 'W' AC_STANDING, 4 Term_Seq union all SELECT 10002 StdID, 2015 Year, 'SPRING' AC_TERM, NULL AC_STANDING, 1 Term_Seq union all SELECT 10002 StdID, 2015 Year, 'FALL' AC_TERM, 'W' AC_STANDING, 2 Term_Seq union all SELECT 10002 StdID, 2016 Year, 'SPRING' AC_TERM, NULL AC_STANDING, 3 Term_Seq union all SELECT 10002 StdID, 2016 Year, 'FALL' AC_TERM, NULL AC_STANDING, 4 Term_Seq union all SELECT 10005 StdID, 2015 Year, 'SPRING' AC_TERM, NULL AC_STANDING, 1 Term_Seq union all SELECT 10005 StdID, 2015 Year, 'FALL' AC_TERM, NULL AC_STANDING, 2 Term_Seq union all SELECT 10005 StdID, 2016 Year, 'SPRING' AC_TERM, NULL AC_STANDING, 3 Term_Seq union all SELECT 10005 StdID, 2016 Year, 'SUMMER' AC_TERM, NULL AC_STANDING, 4 Term_Seq union all SELECT 10005 StdID, 2016 Year, 'FALL' AC_TERM, NULL AC_STANDING, 5 Term_Seq )a [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wow,this is what i was looking,you saved my day :) , i never know about lag function very good information Thank you so much Usman
0 Likes 0 ·
I am glad I was able to help. Cheers.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.