pivoting more than one column in sql server
-
hi everyone, i have sql server db with a table which contains a field for a workplace and the number of people employed in a specific month and year separated by gender. it kinda looks like : workplace month year male female Microsoft feb 2011 5 1 Google feb 2011 6 2 Intel feb 2011 7 3 Microsoft march 2011 8 4 obviously with different actual data. is there a way to concatenate the year and month columns and pivoting the data so it'll look something like : workplace date male female date male female Microsoft feb 2011 5 1 march 2011 8 4 Google feb 2011 6 2 Intel feb 2011 7 3 sorry if that was a bit too much, I'll be happy to explain myself more clearly if needed.
-
hi everyone, i have sql server db with a table which contains a field for a workplace and the number of people employed in a specific month and year separated by gender. it kinda looks like : workplace month year male female Microsoft feb 2011 5 1 Google feb 2011 6 2 Intel feb 2011 7 3 Microsoft march 2011 8 4 obviously with different actual data. is there a way to concatenate the year and month columns and pivoting the data so it'll look something like : workplace date male female date male female Microsoft feb 2011 5 1 march 2011 8 4 Google feb 2011 6 2 Intel feb 2011 7 3 sorry if that was a bit too much, I'll be happy to explain myself more clearly if needed.
-
hi everyone, i have sql server db with a table which contains a field for a workplace and the number of people employed in a specific month and year separated by gender. it kinda looks like : workplace month year male female Microsoft feb 2011 5 1 Google feb 2011 6 2 Intel feb 2011 7 3 Microsoft march 2011 8 4 obviously with different actual data. is there a way to concatenate the year and month columns and pivoting the data so it'll look something like : workplace date male female date male female Microsoft feb 2011 5 1 march 2011 8 4 Google feb 2011 6 2 Intel feb 2011 7 3 sorry if that was a bit too much, I'll be happy to explain myself more clearly if needed.
-
Mycroft Holmes wrote:
There are some excellent articles
Shameless plug, excellent. :-D 5 for that. And it is a very useful article.
-
Hey, Sorry for the late response, I wasn't around a computer. While article you added really shed some light about the topic, I still have a small problem. the article showed how to concatenate to columns into one and then pivot the column, what I want to do is to pivot them both. So if i refer to the article, instead of having a column for "1996-F" and one for "1996-M" I want two columns, one for "M" and one for "F" and "1996" wrapping them. something like: 1996 M F 6 5
-
Hey, Sorry for the late response, I wasn't around a computer. While article you added really shed some light about the topic, I still have a small problem. the article showed how to concatenate to columns into one and then pivot the column, what I want to do is to pivot them both. So if i refer to the article, instead of having a column for "1996-F" and one for "1996-M" I want two columns, one for "M" and one for "F" and "1996" wrapping them. something like: 1996 M F 6 5
Now you are trying to put UI/presentation logic into the database operation. Adding the additional row with the year is not a database operation it needs to be done in your application.
Never underestimate the power of human stupidity RAH