[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL query

Posted on 2012-08-29
Medium Priority
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

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

Accepted Solution

lwadwell earned 2000 total points
ID: 38348639
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38349013
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
ID: 38349572

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 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