• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

SQL query

Hello Experts ,

I have a table test , which has values as like below:

select * from test;

col1 col2 
1	AA
2	BB

Open in new window


Now I want  to get the result as below on SQL Server 2008 version without using pivot:

column_name  data1 data2        
col1 1  2
col2 AA BB

Open in new window

0
Swadhin Ray
Asked:
Swadhin Ray
1 Solution
 
Jared_SCommented:
SQL wont support that in any way that is even moderately efficient. Can you process the data outside of SQL?
0
 
lwadwellCommented:
Agree that it isn't elegant to do this in SQL ... not even sure a PIVOT would work.
This is the SQL I came up with:
;with test as (
select 1 col1, 'AA' col2 union all
select 2 col1, 'BB' col2
)
select column_name, max(data1) data1, max(data2) data2
from (select 'col1' column_name
           , case when rn = 1 then convert(varchar,col1)
             end as data1
           , case when rn = 2 then convert(varchar,col1)
             end as data2
      from (select col1, col2, row_number()over(order by col1) rn from test)v
      union all
      select 'col2' column_name
           , case when rn = 1 then convert(varchar,col2)
             end as data1
           , case when rn = 2 then convert(varchar,col2)
             end as data2
      from (select col1, col2, row_number()over(order by col1) rn from test)v)x
group by column_name

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I wonder also about such a requirement, and can confirm that with "sql", you cannot do this.

in ms excel, it would be a "transpose" function ...
0
 
Swadhin RaySenior Technical Engineer Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now