[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL transpose multiple columns

Posted on 2013-02-01
7
Medium Priority
?
571 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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