Issue with the distinct in select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with the distinct in select

Author

Topic

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-29 : 09:23:48

Hi all,

I have temp table which displays the records as following
Projid Projname E1 E2 E3 E4 E5 E6 E7 E8
100100 CAE - Telvent JV - for Revenue Recog. Only 0 0 2 0 0 0 0 0
100100 CAE - Telvent JV - for Revenue Recog. Only 0 6 0 0 0 0 0 0
100100 CAE - Telvent JV - for Revenue Recog. Only 0 0 0 0 0 0 3 0
100100 CAE - Telvent JV - for Revenue Recog. Only 0 0 0 0 0 0 0 3
100100 CAE - Telvent JV - for Revenue Recog. Only 0 0 0 2 0 0 0 0

For the above table i dont want 0's to display.Istead i need as follows.

Projid Projname E1 E2 E3 E4 E5 E6 E7 E8
100100 CAE - Telvent JV - for Revenue Recog. Only 0 6 2 2 0 0 3 3

Like it should display as one row instead duplication the projid.I gave distinct but still it did not work.
is that possible to get the above table..Appreciate for the solution

Thanks..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-29 : 09:30:27

[code]
select Projid, Projname, E1 = sum(E1), E2 = sum(E2), . . .
from your_temp_table
group by Projid, Projname
[/code]

KH
[spoiler]Time is always against us[/spoiler]