Pivoting in sql
Pivoting in sql means changing rows to columns and columns to rows. We often need to change rows to columns in some scenario. Suppose I have a table say "Users" having columns Id,Name and UserType. Now if there is requirement to find number of users under each UserType I can use GROUP BY clause. But if I want UserType as Column and under them count of users in each UserType I can simply pivot the table we obtain from GROUP BY clause.
Example
1) Here is my Users table.
SELECT * FROM Users
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALUAAAD5CAIAAADuq+PpAAAO8ElEQVR4nO2dPW7rOhOGuSEbrl19WzAgb8G9C9+0ZwdOYVxvIl0utIaUxkG6LEZfoX/L0gznhxlJ84I4SIwT+qX0eEhLo2H4fnwVRfH9+Crbnz9//udy1Qov+SjUpNS5qmeybLrCqHE+ykfzimDrwme/20W6inXufKQ4yr9uhuzc+UhxlH/dDNm5DT4+z7vtOZ9+hdZt2Lx9UvpUHOzjegyH++ivcLtn4VnZVcn54vkIoelnKXyw/zDC+dL52J7fsnC8RfepOFjng3Yi80f9iS8lxEfe7Xx7zvvx+XgrXz8ctyGEsLtc36ofPr57fjrzlA4f+WVTvtPrt25NNn/b9nPP2inmnoVddmhG1P5/9FjM8vHxtq0/67eDGB+Pr3vWcPAqSjXLlO4PlZ/6UMbHnkg+6hc/z7twuA/f+mkt9dRP9Vf1i53/nF82Ibt+x4zFKh9d33LzS14etadA0o1SgwDTOSVdxYWQSD4+RuJW/da34QHp9VN9Bm6HkF2/n45kRQx2LCvk4yu/bHbZYbc954/rMQw+mqN80Cf4cT46H+XBSO9ZCNXp7L/1iwPS5+x2CNn1nnXjX3+M6LFY5aMzv+SXjeD8Uh/Nck3TOazlLDbGR3e+i2dl4vrHvVkyPzqf+Irakp7BW4N8tAPsue3ML9ixmOWjE1e3G9n40WWuWQkC80tz0IXXpyUEz8vw5/Xp01vDfHzds870tN3s+v3jx2KPD5Pdzs1Vf7nK+CbvfKRoSV3dDp3A43z84pmYuatY56N8/NFR88az6JYpm64wAvhQVVEUM+qWKZuuMJriQzuChRCU/vUm1QA+Ch2Vnf9VkJ5njmy6AoXiQxzJpvMQgjgfIYTfPKIjcj4ofIjD8dfjh6g8fiTS2vhg5aTg4se/Wcj+jedj/Ezkp3DKR3/Fi/KHEB9kM7oi8NHcMhDgYyR+/PdPdc+Awsd4/ODz8fO+L40J8kHvM4E8fhDk8SMVH5PrDyIfsfGj+fzu33+KovN7+cLP+35/Ou27n2/nY0Xxo37x530fTnnx876vOKl+/Hnf1+SM9BN3oHHerMjjRxUuBsGjDiF5C8xIP3EHGufNilYVP356p7r/W34KIZzyKoqM/lH9f52PBcaP/klow0d5/tvppPo/5Y/OR1EU64gfRW/+6Cwz+i/kp+f1qfPh10+VBfFhVL/Mhzgcf/3+i6g8fiTSkvlQSlzz+GFfMB+MzDRAHj/sC+ZDu3lyofEG8KEEpl7nep45sukKFIoPcSRV4bN5Jmy6AuV8JJJNV6Ccj0Sy6QoUhY+2LBO1Wh7IR3OJm3DRebxbgfwgsjGlwWormo+6jMRXr5KEKB+DW2TEIQ3E5YNjTGmw2mLNL/ll0z4qrjO/vLg7hh7SQFL5hRRjSoPVFocPrfjRUYr4EZ9fSDGmNFht0fmIqvxE4+NFal/MkAaSyi+kGFMarLaIfHDgQPJBPl6RfETnF0pTS+8zgSh8MOHA8ME5XuPdCuQXKlBrF46CwsftwHkyCsVHfuJ81Zs4E9z8QoYxpcFqK5qPpz0DNL6/tNcDeml/0UN6JVZ+IceY0mC1RZlf+M2vn85Fzkci2XQFyvlIJJuuQKH40MsvVB2SKdl0BQrmg5FACMj5sC+YD28rbwAfSmB6/JiFUHyII+nrj7nI+Ugkm65AOR+JZNMVKAofzSV2WvIHho/mqjPhzsR4t5L5hXJZKazBaiuej3ZDSXqJB/j+XHUfQraSApsPhjGlwWqLM7/0t9dTmV8oGXeq8YNsTGmw2mLwwdjVEcsHKeMulg9KfmG8MaXBaovIR7mHnt76o6hPk/76Izq/kGZMabDaYs4vC8hPJtQvpBhTGqy2OHzM7vkGqfqFFGNKg9VWNB9zfj6Km1/oz0fFXf+gBQ9M/Oin+BGH9Erc+oVkY0qD1RZrfiE3v346FzkfiWTTFSjnI5FsugKF4sPzC/my6QoUzAcjgRCQ82FfMB/eVt4APpTA9PgxC6H4EEfS1x9zkfORSDZdgXI+EsmmK1B0Pu6Z4vX1Um1qFWlIr/qTyQ8iGIMGazF5rKDz8Xnead5/KYrqvohFPkjGxl0tcH/kj7ft5pgRb+7j+KjStuzxQTS2pvhxO4TsSk7+QPGRn15vDRgzpNedDn6Nyy+kGlsPH1XauiYf7ZnT5yMqv5BubC18NFjo8dEc/SR8ROQXcoythI+Pt23v+CnkFw5PkZH8QpaxlfDxIpCorD+KopCPH1L7I3v8WCgfQvsjOx9+/VRaNl2Bcj4SyaYrUM5HItl0BQrFh+cX8mXTFSiYD0YCISDnw75gPrytvAF8KIHp8WMWQvEhjqSvP+Yi5yORbLoC5Xwkkk1XoAh8XI/tLSql+mPdTXNM1R+jG5vgY1H7I3PKFsbwobG/mAgfwvuLLa7+x+d5x9ucEOaDV0dHkQ+GMcz8soT6QWVmMif5A8UHeXZRzS9kGEPwsYz40dbHVatP1z4+kKA+XUx+IcMYyIfNXU4J64+2za0+nWD9QuH6dDbhKFbGh9H6hWbhKGj5yXX9wutR5/tt5xxRnqCbOBNy9QujjU1NpuZWHa0o8cPrFwquT31/ZNb8whmSKdl0Bcr5SCSbrkA5H4lk0xUoFB+eX8iXTVegYD4YCYSAnA/7gvnwtvIG8KEEpsePWQjFhziSvv6Yi5yPRLLpCpTzkUg2XYEi8XE7VFeDqxsxCnww9gwe71ai/hjV2MRgl7U/8uN6rHeuJJe4xOSflgdLtJKkTP4pzdioq6Xtj9zmB6nNL7S6p4MhvepXav9sQVeNDKYXsu7va/HBzLOL5QOfX/jOMAbzYTG9kMTHLjvsNNcf7RlJsf6IyC/kGJvmYzn7I+eXjXb+af+MGNofmWMMN7+YQ4Sy/mjWpFr5hfmpmYdt5RcyjCH4sLgAieejfT5Kb3/15oNua39kjjHEYmsZ8eMx3+sfhUD9Qs3rH9aCR+HXT5PJpitQzkci2XQFyvlIJJuuQKH48PxCvmy6AgXzwUggBOR82BfMh7eVN4APJTA9fsxCKD7EkfT1x1zkfCSSTVegnI9EsukKVDQf+WXTu8FJqlUH37/tKe5S9viZ4OYHcYxBfFhMHiu48ePzvCPdgomIHz/ve7E7HWL7ZxOMjbta4P7IVbtnSvVPW3XuqEcP6UVncnzEGltf/GAUusTyQUqIiOUjbn9kqrHV8ZFfNrTkDzwf8XNLb0gD8fdHphtbGx+sKso4PoiHLJIPQv1CirF18cHZ3BTJBznZbrxbmfqFCruuFgvj4+NtS1yZovmg52JOdCuxPzLR2Jr4uB2Yj8Cg8gtVnjSRyC8kGVsTH+zm10/nIucjkWy6AuV8JJJNV6BQfHh+IV82XYGC+WAkEAJyPuwL5sPbyhvAhxKYHj9mIRQf4kj6+mMucj4SyaYrUM5HItl0BYrCR5NiqLc/0OCaN2VIAwnkB5GNTQx2Wfsjf5531Z19rf3FOoUwZPcXk9j/lmpszNXi6n900sbumU59mM4tMcJnXJcPqjHM/LKM+kHNzX16ihA0vzQHSjYTh59fSDeG4GMZ8ePRbKFNTwGB16f9rC7akAaSyC+kGgP5sLnLKWd+oWcJoecXSsSN5CMmv5BhbJoPm3AUtOejtOsX/rw8I/FDmuyYkl/IMTbBh1k4Ckr8aOtrq9UvZGxzX0yeCW5+IcPY1GDNrTpaUdYfzf7IevULOZcEJiO52P7IUvULfX9k0vpUYkimZNMVKOcjkWy6AuV8JJJNV6BQfHh+IV82XYGC+WAkEAJyPuwL5sPbyhvAhxKYHj9mIRQf4kj6+mMucj4SyaYrUM5HItl0BYrCR3N9Xa9+EGfL4PFuBfILycaUBquteD7a+3Nq+UG8LYMV+WAYUxqstqL5eLq/r7R/Zfc2up38Qo4xxPxiML2QfX9faf/K7m107f3VI/ILGcZgPiymF7LWH5tjprO/aTsl708nwfwggfxCsrFpPpazP/ITKEp8NCI84BDJB6F+IcUYbn4xhwiHD739bxvJ5o/J1C+kGcN8GAwuQKL5yC+bKm3sdqAV5wf56D2FJFpplJlfyDEGD3Yx8WO21z8KwfxCjesf1oJH4ddPk8mmK1DORyLZdAXK+Ugkm65Aofjw/EK+bLoCBfPBSCAE5HzYF8yHt5U3gA8lMD1+zEIoPsSR9PXHXOR8JJJNV6Ccj0Sy6QoUmY9e8ljsE/04Pp7vliCvbI93K7W/KcVY1GDtiMDHx9u2PB41H/EVhSA+BlsGxxTu0eSDbixisJYkET86240hKxpGx4/XPwJDmuoQ1ZOksfXEj2c+CBWnYvmIKuwUy0f8/sgUY87HTPkg7I/sfKyID9b+yM7HktYfUvmFvv6I4kP++0upziGT+f4isj8y0diK+ZjN9Y9CYH9kqrG18cFqfv10LnI+EsmmK1DORyLZdAUKxYfnF/Jl0xUomA9GAiEg58O+YD68rbwBfCiB6fFjFkLxIY6krz/mIucjkWy6AuV8JJJNV6DIfAyL00WUqyNcXx95ZWpIUIcy+YXIriYGu6z6hcP8whevMPkYptxFJOFp8kE3NupqYfULPX6Ix49aBssHOR+8/EK8MZgPi+WDnA9efiHe2PRgl1e/cDF8sPIL8cZw84s5RFbFh0J+IdoYgg+LC5BV8aGQX4g2NuZqgfUL58uHRn4h0tg0tX0DhkTmg9X8+ulc5Hwkkk1XoJyPRLLpChSKD88v5MumK1AwH4wEQkDOh33BfHhbeQP4UALT48cshOJDHElff8xFzkci2XQFyvlIJJuuQJH5mOPz+0brFy48v7DdcQy9I11sfmHUTSxNPujGRl2tJr+wYkWjvlRX4vXp4kUxhphfDN7eF+ZDKn6Uenka5OOHRH4hbAzmw+LtfVE+8sum3lpbiw/MttWRfIjkF8LGpge7/PxCPBxkPpB7mkfyIZBfyKO21401RGT4iIKDxgcSjmLqTKjkF7KpnXyr35YEH/EbJUfzkUfsRzzRrXx+IdrYmKvl5xe2Fz9CCCFofH9przEg8vAmuxXOL8Qbm6bW8wsJfHCHZEo2XYFyPhLJpitQzkci2XQFCsWH5xfyZdMVKJgPRgIhIOfDvmA+vK25NaC85uNXmHUZ1P8BhMJa9CvKF/oAAAAASUVORK5CYII=)
2) To find number of user in each UserType we can use GROUP BY clause.
SELECT UserType,Count(*) As 'Number of Users' FROM Users GROUP BY UserType
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMEAAABMCAIAAABbDkTNAAAFb0lEQVR4nO2dT7KiMBDG+0JYrl15BarwCuxdOG7fDXRhPS7hzinOwJKacudhMgsgJBHS4U9CAv1VakoZjJ3Ojy8B8hCOxyNj7F0Ws5efn5+qckv12yvhRr5IioAYWlPki6SIGFpV5IukaBGGbieIs963eMkSUJXcXET+Ou8hur6Et7tzbl7z0P2xDJwec2Z1dIqCZMh2mrQMAXAOFmOos+HE0GyptBn567zfna9JYwBLMdRdDzFUFvklqsal/eVZZ6oeqKLrq0pcfNoBtJ9t68mSdjjLEtgncbOzsL9S4ViGct6FyltpS/3t+8vtumsaJWzvjaqjmeI+cVbeTiDXMFNWr8qeATLUbHyd9xBn5fO6a3qad4za90I99aeajcLO+SWC5Pb+rnA0Q2WRVVakYYj3UNtV1fbaw3qj6mjm87qTPzXAh8yzquwZJEPP9niVDpfmoHl8J06qp+7XR6xmuUrKd4X9VoQy9C5vJ05Ptw8JRvW9XRPVdzM19c+ZVXlP7xkSjoZSdYUsAaiTKx8NHYmTs/aIIblliXDcK3k3nh8YMFTkl2ifxJMY6ozqu5mmDM2T1XpP7xlqPEPoe+G0ucpFa+DtIatnqJoo8D5r/ksYNeQKpzEkfJ0QxiMGPUNoVB3NNBzLpmVV3dN7hrhzgnCqrM7+2snjl7V0M1RkiWDau2gv169WOJWhIr9EVeU8cthFiA+B2mqDZgpjkFLtrFn92tN3hmwUeYo94SyarlMPStFaGHrE0gFEDFkua2SIIl8uRTVDP3bEv8ZS/fYUbuTOJDFkVYwx219hSeFG7kwtQ7Z9DwAC/ZeKvkgMMTuqKv8XpgDAUlrWoQ6GZoe0GjLfZQEAS/MwRvYOrXXIKUNLwzBS5EN6kQ/hIh/Sy5ChSeuYzHzoN4Hk1xEVw4T6UJ7C4f6x3le+CmWI33mZgaEeH/r7p74J5ClDiA997gcghhgjH9JI60Of++GQpgdiiLlhSDsf8pchnQ/lKaT5504MMUY+pFG/D+UppHnlRcQQ+ZBGfT7E0SGGqhfkQ73q8aHP/QCiNosR+RAu3XyIMUY+RD6ECr0+RAxVL+g6da9QH9q4nDK0NAwjRffL9CIfwkU+pFcHQ5aWS5IPrVUqQxPWQyIiH1qrVIZsFx8WttJy2NmLxJAlVK1WblvhRu5GHQzNDimfDy3a0vEKN3I3IoZwhRu5GxFDuMKN3I1whtpnr4595CrKUJ42X5Hmjto9RCaRexm4IyEMNU/HKaQH5MzK0Od+aHpAeOmTehlq11HXC4m2qQFjWX6JDJ+vNnos8/PmpUHkfgbuSOYM2fIhQaH5EJengTuSKUP8UV/2GPrcD36u49JHXi1F2y5BhgxNAciQIW8BYqZj2XYxwhmaCJAJQz4DxMzO7bc8IcIYesRT/jrRiKE89Xwo6Ivc/zNKN0IYUn6Yx8Z5WXtxyNeV7Rof4sF7GLYzGc2HJha6Tr1uEUO4wo3cjYghXOFG7kYdDNlbC7toS8cr3MjdSGVowmJXROH2RLiRu5HKEBUqI4rEkCVUyYdWrA6GZoeU5kPrFjGEK9zI3YgYwhVu5G6EM8Rvd4xbPGTCkOcrSvsjF+/TbPduB8ZQ9ZuSZTHl8TH4PVe/V5RqGfIxYMcyH8uMft1z2ljm6QKK3sg9jde1jBka8nPMIxnydQGFjiEayQasY7Q5H2J+ryg1GIV9xd+JBo1ltCa/V1se1swZor8N0snPyN0IYYj+RpEZrYXd9K++4D7Erw+NMyETH/J8RWm4kbvRgLFsdKHr1OsWMYQr3MjdiBjCFW7kbtTBEK2FVRRu5G6kMjRhsSuicHsi3MjdSGWICpWhhcMEx+NxIY5JK9F/Unr6R2k3asMAAAAASUVORK5CYII=)
3) Now pivoting table.
SELECT 'Number of Users' As UserType,[1],[2],[3] FROM (SELECT UserType FROM Users) AS SourceTable
PIVOT
(
Count(SourceTable.UserType)
FOR UserType IN ([1],[2],[3])
) AS PivotTable;
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMIAAAAnCAIAAADSCgBXAAAEf0lEQVR4nO2aQa6qMBSGz4YkHTtiCyR1C8wdiFN3oAMim3AmYQ0OjWHmYngDSnsKtuVSaouPk8ZcmvJTej7+U71A4yziOHYn/gNhvz4hKLQi0DRN/XzM3k6nE5+iC/2lN/v1CUGBi6wY+WkhQLBitPgWAgSLxui8g6RQHppbQaEf9OziFpw2UwrNy6JREEukXZkxCrt81I0sDyPrE4No6vW5HaM2gxMxqg6bjp7bMdJxoJxDnkC0r8Yt8oqRz+bUjXirDhtyuFko3I7R5ng3TyMUjKrDpn0G2W3f94TZ6uZ4f9T3PYmSXYSfUaFTUOHeBQVCk24wGt8T/C8wmupGvN33ZFFu1HWyeaOH4L4n0b6670k//UhH3O15B0mBBncOPxD0B9C4FM6AUXXYgPZO9Qrtg72svRHbEAysqPOPfJh7SaegsMsfdZ4APUugtIQNBX0bkmuMjAyNmMPD6Ge1P4zkcit7Q0EBWNblRfxgIfJC5wnQc0F5BUQYMTMLayPlFKMxDI2YA5PS7K5qfxh1zoHSX4sy1EKGHgJuJ3qMnucdAFs7BA0qarKgP4DGpdACozwxfsvTK4z/rld7xAh9pwX+0PS32C0W0hZbj9GjoKgmRhsi6/cFfxej3u9q076pcRG9FfnFyEWTd9wBbKJdr08ICvVPYZQn0nOzYvTFHP0QRgtsIUAwM0YnN8Gn6Eh/6WG/PiEotCIQu4ymaZzqLz3s1ycEhTiOwbV7A8D6qfn8jQb189G4iTiO6+fjtYY6AMByke3TN4sCw2h2PNuS2T5zvpMVbgQCgb2Cc4x8ZyroWN1odaMZIhAI7BU0GFm9F2ZwozIjQLISHaIjc/x1vDquFACAXnt9uKN3OGcY3ahKYXt5T4OgStk/NNLKAIFJAEA7CxVGY9/jnO5GZUYABEjeMPqIyPcwMjjB+7LVJ1ADgQCwSrUgaTHSEygU/LkRyTKeH18YfdYJxI3el+02TbeT3Qjr6L1k0nmSgnOMlG5EspJnsXco9VBKAABIds3YH+UL9YuK1DpcNwSNQRCIMfTKCtqgpikw4qeyOaovl/VGqkMHQZVCWhlTacbofdlOc6P3ZTumpHl3oxKnSIURT5LIVtvPUl1mBEl8Ole6qnzWH9yo6xye2b9cb6Qu1G7ECoolRi0JE/dGoiwaSPTtRm2SOECf3QjZ1bCfZ0t4Q2cE1wEiGn0zRqXwwhe2ouHl5JHaUKWQ0zODG42AwKRgmIZ/N3rxamCDETMD+dEfIjIWI7lXPrpSAEat4XJspI4ilRuhcmKqKbNAYK/g3Y26BSdZiW3gSkGPEdqd862L1PUBkZFFTbYjYUbaGeAZQm/SyjCmcLIbIQua6EboNMPPDkG4EV55tG0lBjdC9UTCUdrzDpLY2ycrE40ugFxT7lBebjBSGeq9kcil/e9GX1BYf8X2GWMKij4CUVj/p+YzjG70HQjsFVY38hmBQGCvwDCyeYdS827l6kb6+Ck3sn+HUhWrG+kjEAjsFb7xHicE8K5qyJ9Lb03T2JrqGms0TfMP+MBOQlLxtacAAAAASUVORK5CYII=)
If I only want value of UserType as column and count of user then I can use the following query.
SELECT [1],[2],[3] FROM (SELECT UserType FROM Users) AS SourceTable
PIVOT
(
Count(SourceTable.UserType)
FOR UserType IN ([1],[2],[3])
) AS PivotTable;
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAGYAAAApCAIAAABIs8uMAAAB/klEQVRoge2ZMY6EIBSG34U01FZ7BRPmCvZTqK03GAvjXMJuDGfYcrPZzsO4hYpaCMjDwUFeyHbz5/H5gchC4WtnQVEUX7721IisP6yMhONDDLYxI/v7+TY+ls/DYojZNjwyj8x1ZI8bxE9EyJPCWPSBTLhVZ0fW5OHQqj4ylgYTqSYPRXPebKOKIbwztYdnHdkwsJYt8ZG0QSQ0eRjkrwsh07WMj9edXMoylgbT+tJJYGnwEXuZMWRSXirQpZ66g0yFlxoyyW7oCrIqVnnhChLU37mOIJsPZQAAoPfG5CFixc6D7E0hLp3+PbKrIZNfR2oV79VuiME2oDj+Vrbv+zOEmGpjRHbEqlwOADDy1/pYIeuPqSH8F134Dk0lrJAZfyY8HACQyADgWsi8Zd4yETKlbxqkZTUFkrXalrFk/OhJmB4yHgAAUdnpI9tx14yyrM0IyJFtWsaSaZosEUITIhPTVkX2HsvajBBKCcayqboykjuy/3eWkG1aVlOgdZvJkcn3sq6M9CzrykhlWZ7DspoCrQfTcJYNs9bcy+alLaFu3zJOyoxlahOWRghEs25Zm5H1HaGQmqkJfwwy8bkMY9lCLU3LFj+bl6h9ZGIcyL2MH6tUNm98gj/9707w35hnReasZZhL3q1y2TLE7a6knLXs6OHGLXa//A+TL8X6BwCoOTTw1wLRAAAAAElFTkSuQmCC)
0 Comment(s)