Solved

How convert rows to columns using SQL?

Posted on 2014-09-23
4
159 Views
Last Modified: 2014-09-30
I want to convert rows from the result to column.
Attached Input file is the sample data, I want to the sql server query to return like the result in Result file.
Input.xlsx
Result.xlsx
0
Comment
Question by:Raj G
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40340439
Hi,

you can use this:

select col2, sum(Test1) as Test1, sum(Test2) as Test2, sum(Test3) as Test3
from(
select col2 , 
case when col1='TEST1' then 1 else 0 end as Test1, 
case when col1='TEST2' then 1 else 0 end as Test2, 
case when col1='TEST3' then 1 else 0 end as Test3
from table_name
group by col2 
)a group by col2

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40340499
input/resultThis query will match the requested result:
SELECT
        param
      , max(case when col1 = 'TEST1' then col3 end) AS test1
      , max(case when col1 = 'TEST2' then col3 end) AS test2
      , max(case when col1 = 'TEST3' then col3 end) AS test3
FROM Input
CROSS JOIN (SELECT 'COL2' AS param UNION ALL SELECT 'COL3' AS param) cj
GROUP BY
        param
;

Open in new window

But what happens to 'english' as a value from the input table?

{+ edit, details}
    CREATE TABLE Input
    	([COL1] varchar(5), [COL2] varchar(7), [COL3] int)
    ;
    	
    INSERT INTO Input
    	([COL1], [COL2], [COL3])
    VALUES
    	('TEST1', 'english', 3),
    	('TEST2', 'english', 3),
    	('TEST3', 'english', 3)
    ;

**Query 1**:

    SELECT
            param
          , max(case when col1 = 'TEST1' then col3 end) AS test1
          , max(case when col1 = 'TEST2' then col3 end) AS test2
          , max(case when col1 = 'TEST3' then col3 end) AS test3
    FROM Input
    CROSS JOIN (SELECT 'COL2' AS param UNION ALL SELECT 'COL3' AS param) cj
    GROUP BY
            param
    

**[Results][2]**:
    
    | PARAM | TEST1 | TEST2 | TEST3 |
    |-------|-------|-------|-------|
    |  COL2 |     3 |     3 |     3 |
    |  COL3 |     3 |     3 |     3 |



  [1]: http://sqlfiddle.com/#!3/b84060/5

Open in new window

0
 

Author Comment

by:Raj G
ID: 40341651
Is there a way I can have 'english' value also in the result set?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40343229
You need to ensure that each column is a data type that can deal with the source data, so here I have converted col3 to varchar; but this is only because it appears col3 is an integer, if it already a (n)varchar then the conversion would not be needed.
SELECT
        param
      , max(case when col1 = 'TEST1' and param = 'COL2' then col2 
                 when col1 = 'TEST1' and param = 'COL3' then cast(col3 as varchar) end) AS test1
      , max(case when col1 = 'TEST2' and param = 'COL2' then col2 
                 when col1 = 'TEST2' and param = 'COL3' then cast(col3 as varchar) end) AS test2
      , max(case when col1 = 'TEST3' and param = 'COL2' then col2 
                 when col1 = 'TEST3' and param = 'COL3' then cast(col3 as varchar) end) AS test3
FROM Input
CROSS JOIN (SELECT 'COL2' AS param UNION ALL SELECT 'COL3' AS param) cj
GROUP BY
        param
;

Open in new window

| PARAM |   TEST1 |   TEST2 |   TEST3 |
|-------|---------|---------|---------|
|  COL2 | english | english | english |
|  COL3 |       3 |       3 |       3 |

http://sqlfiddle.com/#!3/b84060/10

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), 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, f…

867 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

24 Experts available now in Live!

Get 1:1 Help Now