Solved

SQL transpose multiple columns

Posted on 2013-02-01
7
555 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 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

16 Experts available now in Live!

Get 1:1 Help Now