convert Columns to rows

Question

I have data in below format in sql server YRC a b c d e f 201903 305.39 983 -95.71 -814.68 72.61 378 201803 198.56 693.93 -207.34 -379.77 106.83 305.39 201703 193.25 688 -342.1 -340.58 5.32 198.56 Need to convert in below format 201903 201803 201703 Columnname 305.39 198.56 193.25 a 983 693.93 688 b -95.71 -207.34 -342.1 c Please help

Venkat Mungari · Answer

Table: CREATE TABLE [dbo].[tbltest]( [yrc] [ int ] NULL , [a] [ decimal ](18, 2) NULL , [b] [ decimal ](18, 2) NULL , [c] [ decimal ](18, 2) NULL , [d] [ decimal ](18, 2) NULL , [e] [ decimal ](18, 2) NULL , [f] [ decimal ](18, 2) NULL ) ON [ PRIMARY ] Insert Queries : INSERT [dbo].[tbltest] ([yrc], [a], [b], [c], [d], [e], [f]) VALUES (201903, CAST (123.00 AS Decimal (18, 2)), CAST (21.00 AS Decimal (18, 2)), CAST (12.00 AS Decimal (18, 2)), CAST (12.00 AS Decimal (18, 2)), CAST (45.00 AS Decimal (18, 2)), CAST (19.00 AS Decimal (18, 2))) INSERT [dbo].[tbltest] ([yrc], [a], [b], [c], [d], [e], [f]) VALUES (201803, CAST (121.00 AS Decimal (18, 2)), CAST (53.00 AS Decimal (18, 2)), CAST (76.00 AS Decimal (18, 2)), CAST (872.00 AS Decimal (18, 2)), CAST (92.00 AS Decimal (18, 2)), CAST (93.00 AS Decimal (18, 2))) INSERT [dbo].[tbltest] ([yrc], [a], [b], [c], [d], [e], [f]) VALUES (201703, CAST (97.00 AS Decimal (18, 2)), CAST (86.00 AS Decimal (18, 2)), CAST (75.00 AS Decimal (18, 2)), CAST (939.00 AS Decimal (18, 2)), CAST (662.00 AS Decimal (18, 2)), CAST (527.00 AS Decimal (18, 2))) SQL QUERY : select * FROM ( SELECT yrc,a,b,c,d,e,f FROM tbltest ) AS P unpivot ( [Value] FOR [ OutPut ] IN (a,b,c,d,e,f) )unpvt pivot ( max ([Value]) for yrc in ([201903],[201803],[201703]) )pvt OUTPUT :

Abhijeet Jadhav · Answer

There are multiple options to achive rows to columns. Pl find the details below : https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql

Sanjay Gangurde · Answer

Pivot can help to achieve the same. Eg: CREATE table #yourtable ([Id] int , [Value] varchar (6), [ColumnName] varchar (13)) ; INSERT INTO #yourtable ([Id], [Value], [ColumnName]) VALUES (1, 'John' , 'FirstName' ), (2, '2.4' , 'Amount' ), (3, 'ZH1E4A' , 'PostalCode' ), (4, 'Fork' , 'LastName' ), (5, '857685' , 'AccountNumber' ) ; select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from #yourtable ) d pivot ( max (value) for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber) ) piv;

Nikunj Satasiya · Answer

Hi Mayur, Please follow this article, where you can learn detailed information about "PIVOT" and "UNPIVOT" with a simple example. I Hope This article helps you. https://www.codingvila.com/2019/05/pivot-and-unpivot-in-sql-server-with-example.html

Rajeesh Menoth · Answer

Hi, Column to row is a "UNPIVOT" concept in sql. You can refer the following examples. Reference : https://www.codeproject.com/Tips/1166949/Convert-column-into-rows-without-using-pivot https://stackoverflow.com/questions/5337380/converting-columns-into-rows-with-their-respective-data-in-sql-server https://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html

YRC	a	b	c	d	e	f
201903	305.39	983	-95.71	-814.68	72.61	378
201803	198.56	693.93	-207.34	-379.77	106.83	305.39
201703	193.25	688	-342.1	-340.58	5.32	198.56

convert Columns to rows

Answers (5)