Solved

SQL transpose multiple columns

Posted on 2013-02-01
7
558 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

9 Experts available now in Live!

Get 1:1 Help Now