Solved

SQL transpose multiple columns

Posted on 2013-02-01
7
560 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
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 500 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

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