Manipulation on table in SQL Server

Question

I have table MailSummary:

Columns:

Send(Int), Reply(Int), SRT(Nvarchar(10)), NAME(Nvarchar(10))

|Send| Reply| SRT| Name|
------------------------
| 100|     8|  10|    B|
|  30|     2|  20|    R|
|   5|     0|  30|    S|
-----------------------

I want to transform the table and get a new result set like:

Sum(Send), Sum(Reply)

and show the name in columns and the value of SRT under the new columns

|Send| Reply| B | R | S |
-------------------------
| 135|    10| 10| 20| 30|

How can I do it in a SQL query?

does this only need to work for three values (B, R, S) or dynamically? — Fabian Bigler, Jun 22 '13 at 09:52
Hi,It does not matter. both of them will be very helpful, 10x. — user3020789, Jun 22 '13 at 10:12
There are already many answers on SO: http://stackoverflow.com/questions/8134063/select-rows-as-column-in-select http://stackoverflow.com/questions/1578500/how-to-select-columns-as-rows — Fabian Bigler, Jun 22 '13 at 10:20
Thanks about your examples, but how can I sum the two columns (Send,Reply) and also select the rows into columns. I need an example if you can demonstrate on my question it will be very helpful. — user3020789, Jun 22 '13 at 10:33
Why is `SRT` an `Nvarchar(10)` when it apaprently stores integers? — Martin Smith, Jun 22 '13 at 10:35

Fabian Bigler · Answer 1 · 2013-06-22T10:50:39.130

1

This works fine for your scenario:

SELECT SUM(Send) as Send,
       SUM(Reply) as Reply,
       MIN(CASE WHEN Name = 'B' THEN SRT END) AS B,
       MIN(CASE WHEN Name = 'R' THEN SRT END) AS R,
       MIN(CASE WHEN Name = 'S' THEN SRT END) AS S
FROM   TEST

Though for a more dynamic query, you will want a dynamic cross tab query.

Example on fiddle: http://sqlfiddle.com/#!3/d0ddc/12

edited Jun 22 '13 at 10:50

answered Jun 22 '13 at 10:28

Fabian Bigler

10,403
6
47
70

This does four queries against `TEST`. Only one is needed. – Martin Smith Jun 22 '13 at 10:29
thanks. I will check the cross tab query. you were very helpful. have a nice weekend. – user3020789 Jun 22 '13 at 10:42

Manipulation on table in SQL Server

1 Answers1