question

atreau2k avatar image
atreau2k asked

How do I convert the SUM() results of '6 columns and 1 row' to '2 columns and 6 rows' of the same results?

using this query: select sum(Interviewed) as daInterviewed, sum(Hired) as daHired, sum(Fired) as daFired, sum(Suspended) as daSuspended, sum(FirstDayNoShowed) as daFirstDayNoShowed, sum(Quit) as daQuit FROM table where year(DateCreated) = 2012 and month(DateCreated) = 11 I get accurate results that look like this: daInterviewed daHired daFired daSuspended daFirstDayNoShowed daQuit 27                     23        16             19                       3                    8 however, I need it to look like this: 1 daInterviewed             27 2 daHired                     23 3 daFired                     16 4 daSuspended            19 5 daFirstDayNoShowed 3 6 daQuit                       8 its kinda a weird request, but my pie chart i need to dynamically plug these values into only takes 2 columns of data.. can anyone help????
sqlsum
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
You can use the [`UNPIVOT`][1] operator select value, daState from (select sum(Interviewed) as daInterviewed, sum(Hired) as daHired, sum(Fired) as daFired, sum(Suspended) as daSuspended, sum(FirstDayNoShowed) as daFirstDayNoShowed, sum(Quit) as daQuit FROM table where year(DateCreated) = 2012 and month(DateCreated) = 11 ) t1 unpivot (daState for value in (daInterviewed,daHired,daFired, daSuspended,daFirstDayNoShowed,daQuit) ) t2 gives you value daState -------------------------- ----------- daInterviewed 27 daHired 23 daFired 16 daSuspended 19 daFirstDayNoShowed 3 daQuit 8 [1]: http://msdn.microsoft.com/en-us/library/ms177410%28SQL.105%29.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

amazing.. this worked perfectly.. Kev >> u da man!!!
0 Likes 0 ·
Thanks! If it answers you question, please accept it as the answer by clicking the tick mark - that way it'll help future readers with similar questions.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.