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

Question

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.

codeworx · Accepted Answer · 2014-06-24 11:40:38Z

2

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;

answered Jun 24, 2014 at 11:40

codeworx

2,74515 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SudeepShakya Over a year ago

If number of rows are unknown, then how can I get desired result ?

codeworx Over a year ago

i'm afraid not without creating and executing the Statement dynamically. With this statement you can execute dynamic SQL.

Ankit Bajpai · Accepted Answer · 2014-06-24 11:24:53Z

0

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.

answered Jun 24, 2014 at 11:24

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related