x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 581

# SQL transpose multiple columns

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
Cluskitt
1 Solution

Commented:
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

Commented:
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
``````

Giannis
0

Author Commented:
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

Commented:
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

Author Commented:
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

Commented:
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).
0

Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.