I want to transpose data like

CREATE TABLE #TEMP (Dates DATE,ColumnName1 int, ColumnName2 int, ColumnName3 int, ColumnName4 int,ColumnName5 int)
 INSERT INTO #Temp
 Values
 ('2018-04-01', 1, 2,  3, 4, 5)
,('2018-04-02', 6, 7,  8, 9,10)
,('2018-04-03',11,12, 13,14,15)
,('2018-04-04',16,17, 18,19,20)
,('2018-04-05',21,22, 23,24,25)

--if your server is old use unpivot instead of cross apply

;WITH CTE AS(select dates,colsdata,colname from #temp
   cross apply (values(ColumnName1,'ColumnName1'),(ColumnName2,'ColumnName2'),
                      (ColumnName3,'ColumnName3'),(ColumnName4,'ColumnName4'))AS allcol(colsdata,colname)
			 )

select * from CTE pivot(max(colsdata) for Dates in([2018-04-01],[2018-04-02],[2018-04-03],[2018-04-04],[2018-04-05]))AS Pvt

--OUTPUT:-
------------------------------------------------------------------------
colname	     2018-04-01	2018-04-02	2018-04-03	2018-04-04	2018-04-05
---------------------------------------------------------------------------
ColumnName1	      1	       6	      11	      16	      21
ColumnName2	      2	       7	      12	      17	      22
ColumnName3	      3	       8	      13	      18	      23
ColumnName4	      4	       9	      14	      19	      24

There are two SQL routes:

1) Create a #temp table and put the data into it in your desired (transposed) view and then do simple queries against. Or, even build a VIEW, if this is to used often, and then it's always painlessly available

2) Use a series of INNER JOIN's on the same table. I use this to convert data in columns to data in rows.

Something like:

SELECT A.field1 AS Column1, B.field1 AS Column2, C.field1 AS Column3,. . . .
 FROM Table AS A INNER JOIN Table AS B 
   ON A.somekeyfield=B.somekeyfield
 INNER JOIN Table as C
   ON A.somekeyfeld = C.somekeyfiled

. . .
You will need to "put on your thinking cap" to see how to string these together in your context. What you will notice is that each single column selected from the table is now a member of a row.