Converting rows into columns tsql

Question

I have a table like

CId| RId|  No
---+----+----
  1|  10| 100    
  1|  20|  20    
  1|  30|  10    
  2|  10| 200    
  2|  30|  20    
  3|  40|  25

here, RId represents "NoToAttend" (10),"NoNotToAttend" (20),"NoWait"(30),"Backup" (40) etc...

I need to have a result table that will look like

Cid|  "NoToAttend"|   "NoNotToAttend"| "NoWait"|  "Backup"
---+--------------+------------------+---------+----------
  1|           100|                20|     null|
  2|           200|              null|       20|     null
  3|          null|              null|     null|       25

I am not sure on how to use PIVOT. Need help on this

One more answer already available @ stackoverflow.com/a/3586937/1661861 — Saravanan
– Saravanan, Commented Dec 5, 2013 at 13:28

GarethD · Accepted Answer · 2013-12-05 13:14:17Z

1

You can use the PIVOT Function and just alias your columns:

SELECT  pvt.CID,
        [NoToAttend] = pvt.[10],
        [NoNotToAttend] = pvt.[20],
        [NoWait] = pvt.[30],
        [Backup] = pvt.[40]
FROM    T
        PIVOT
        (   SUM([No])
            FOR RID IN ([10], [20, [30], [40])
        ) pvt;

answered Dec 5, 2013 at 13:14

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting rows into columns tsql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related