CODEWITHSUNDEEP
sqlsql-server
SudeepShakya
SudeepShakya

Reputation: 621

Changing single column data with multiple rows to multiple column data with single row

My query is :

SELECT TOP 2 trans_date_np FROM day_in_status_mcg ORDER BY day_in_id DESC

Result :

2070÷10÷02  ->row 1
2070÷10÷01  ->row 2

Can I get result like this :

column 1       column 2
2070÷10÷02     2070÷10÷02

I tried to use XML PATH. 'trans_date_np' is varchar.

Upvotes: 1

Views: 35

Answers (2)

codeworx
codeworx

Reputation: 2745

If you know how many rows you have you can use the pivot statement.

SELECT [1], [2], [3], [4], [5]
FROM
(SELECT TOP 5 trans_data_np, ROW_NUMBER() over (order by day_in_id) as rowNumber  
    FROM day_in_status_mcg
    ORDER BY day_in_id
    ) AS SourceTable
PIVOT
(
min(trans_data_np)
FOR rowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable;

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can use following query:-

SELECT MAX(trans_date_np) AS column1, (SELECT MAX(trans_date_np) 
                                       FROM day_in_status_mcg 
                                       WHERE trans_date_np< SELECT MAX(trans_date_np) 
                                                            FROM day_in_status_mcg) AS column2
FROM day_in_status_mcg;

I hope this will solve your problem.

Upvotes: 0

Related Questions