SQL query

Posted on 2012-08-29
Last Modified: 2012-08-30
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

Question by:Swadhin Ray
    LVL 12

    Expert Comment

    SQL wont support that in any way that is even moderately efficient. Can you process the data outside of SQL?
    LVL 25

    Accepted Solution

    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

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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 ...
    LVL 16

    Author Closing Comment

    by:Swadhin Ray

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now