CODEWITHSUNDEEP
sql-server
Affan
Affan

Reputation: 25

Convert row to columns

I am stuck with a problem in SQL Server.

My query is like this:

select Format(new_qtrdate1,'dd-MMM') as MTBURDATE1,
       Format(new_qtrdate2,'dd-MMM') as MTBURDATE2,
       Format(new_qtrdate3,'dd-MMM') as MTBURDATE3,
       new_mtbur1 as MTBUR1,
       new_mtbur2 as MTBUR2,
       new_mtbur3 as MTBUR3
  from Filterednew_craform
 where

The current result of my query looks like this:

(MTBURDATE1) (MTBURDATE2) (MTBURDATE3) MTBUR1 MTBUR2 MTBUR3
------------------------------------------------------------------------
01-May         01-Jun        01-Jul    34214  10756   9879

What I want to get is this:

Date    MTBUR
------------
01-May  34214
01-Jun  10756
01-Jul  9879

How can I do that?

Upvotes: 1

Views: 51

Answers (2)

Pintu Kawar
Pintu Kawar

Reputation: 2156

If you have fixed number of columns try below:

with CTE as
(
    SELECT....... -- Your Query
)

Select MTBURDATE1 AS Date, MTBUR1 AS MTBUR from CTE
UNION ALL
Select MTBURDATE2, MTBUR2 from CTE
UNION ALL
Select MTBURDATE3, MTBUR3 from CTE

Upvotes: 3

RealCheeseLord
RealCheeseLord

Reputation: 795

I think that is UNION ALL should do what you Need:

SELECT (MTBURDATE1)
       , MTBUR1 
  FROM Filterednew_cra
 UNION ALL
SELECT (MTBURDATE2)
       , MTBUR2 
  FROM Filterednew_cra
 UNION ALL
SELECT (MTBURDATE3)
       , MTBUR3 
  FROM Filterednew_cra

Upvotes: 1

Related Questions