?
Solved

SQL transpose multiple columns

Posted on 2013-02-01
7
Medium Priority
?
563 Views
Last Modified: 2013-02-01
I have a query that produces something like:

Sex    Jan   Feb   Mar   Apr .....
M       5      3       0       2
F        3      3       2       1

Let's say it's something like:
SELECT Sex,Jan,Feb,Mar....
FROM Table

to keep it simple. I want a full transpose, so I get something like:
Month  M  F
Jan        5  3
Feb       3  3

etc... I've tried messing around with unpivot, but I can't do it for more than one column.
0
Comment
Question by:Cluskitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Expert Comment

by:zombie2020
ID: 38843262
something like this:
select 'Jan' as Month,
            max(case when sex='M' then jan else null end) as M,
           max(case when sex='F' then jan else null end) as F
from table
group by sex

union all

select 'Feb',
            max(case when sex='M' then Feb else null end) ,
           max(case when sex='F' then Feb else null end)
from table
group by sex
...
...
...
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 2000 total points
ID: 38843291
Hi,

Have a look at the following example:


--This section actually creates temp data------------------------
declare @table table 
(
    SEX char(1), 
    JAN int, 
    FEB int, 
    MAR int, 
    APR int
)

INSERT INTO @table
SELECT 'M' as SEX,
			5 as JAN,
			3 as FEB,
			0 as MAR,
			2 as APR
union all
	SELECT 'F' as SEX,
			3 as JAN,
			3 as FEB,
			2 as MAR,
			1 as APR
-------------------------------------------------------------------------------------------

SELECT * FROM
(
	SELECT * FROM
	(SELECT * FROM @table) T --Here you should change @Table with your table
	UNPIVOT
	(SEX1 FOR MonthName IN (JAN, FEB, MAR, APR)
	) AS Unpvt
) y
PIVOT
( AVG(SEX1) FOR SEX IN ([M],[F])) AS pvt

Open in new window


Giannis
0
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 38843322
Excellent. Just what I was looking for. Thanks a lot.

@Zombie2020: While your approach would work, it would involve creating 12 full queries, which would also affect performance. Thank you for your input, though.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 38843349
Cluskitt,

While the method proposed by jyparask works with your sample data, it is not extensible (will not work unless you have a very limited dataset and you know all of the values ahead of time).

The UnionAll method provided by zombie2020 is the "approved" method to normalize data.

Just my 2 cents.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38843378
I'm not sure I understand you. I need to know the values to transpose (in this case months and sex) no matter which method I use. If I don't know the possible outputs I can't use either method.

The biggest advantage I see in zombie2020's method is that it's easy to read and adapt, whereas unpivot/pivot is harder to grasp (at least for me).
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38843403
the sample prodided by jyparask is specific you the data you presented.  You must know the row (sex), column (months), and cell values to use that query.  It will only work for this set of data.

I realized when I took a closer look at zombies example that it is not precisely what I would have used as a "normalizing" query.  Forgive the image, but my IT guys have somehow managed to block some of my query constructs (probably trying to avoid a SQL injection attack).sample normalizing query
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38843537
Maybe I'm just not understanding, but I don't think your query does what I want. Your query will return two rows per select, one for each sex, whereas what I wanted was to have one row per select, pivoting the sex to a column.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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