convert Columns to rows

Mayur Gujrathi

6y1.5k

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sanjay Gangurde6y ago
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 Satasiya6y ago
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
Venkat Mungari6y ago
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 :
Rajeesh Menoth6y ago
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
Abhijeet Jadhav6y ago
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