Display 1 field in 3 column - SQL Server Forums

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

Development Tools

ASP.NET

Display 1 field in 3 column

Author

Topic

thalz142
Starting Member

7 Posts

Posted - 2008-01-29 : 00:23:34

Hello,

im using asp.net 2.0..

Here is my COMPANYTABLE structure

field1Company field2Status

Company1 Done

Company2 Done

Company2 Uncomplete

Company3 Done

Company3 Uncomplete

_______________________________________________________________

the problem i wanna display my data by column like this

company | Done | Uncomplete
comp1 | 1 | 0
comp2 | 1 | 1
comp3 | 1 | 1

CompanyColumn = maybe i can use select distinct to make sure it will not redundant for company.

StatusColumn=Done AND StatusColumn=Uncomplete IS IN ONE FIELD HOW TO DEVIDE BY COLUMN??

using count statement.but how?? try to using gridview unfortunately not suitable for this dimension..

What is the best way to display my data to be like this.Any idea or reference will be appreciate! thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 00:29:34

[code]SELECT field1Company,
[Done] = SUM(CASE WHEN field2Status = 'Done' THEN 1 ELSE 0 END),
[Uncomplete] = SUM(CASE WHEN field2Status = 'Uncomplete' THEN 1 ELSE 0 END)
FROM COMPANYTABLE
GROUP BY field1Company [/code]

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

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-29 : 00:33:15

try this
Declare @Table1 table
(

Comp int,
Stat varchar (30)
)

Insert @Table1
Select 1, 'Done' union all

Select 2, 'UnDone' union all

Select 2, 'Done' union all

Select 3, 'Done' union all

Select 3, 'UnDone'
Select comp, SUM (Case when stat='Done' then 1 else 0 end) [DONE],SUM (Case when stat='UnDone' then 1 else 0 end) [UnDONE] from @Table1 group by comp

thalz142
Starting Member

7 Posts

Posted - 2008-01-29 : 00:54:25

Hi khtan thanks!
just use this statement and its work..hmm must study about his statement even didnt really understand .

SELECT field1Company,
[Done] = SUM(CASE WHEN field2Status = 'Done' THEN 1 ELSE 0 END),
[Uncomplete] = SUM(CASE WHEN field2Status = 'Uncomplete' THEN 1 ELSE 0 END)
FROM COMPANYTABLE
GROUP BY field1Company

__________________

To:sunil
im new to sqlserver.can i know where to put ur statement??or give some clue and i the rest i can dig my self..thx sunil for explaination.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-29 : 01:11:33

You can use it in your SP. I tell you when I needed this kind of requirement-When showing up this kind of result set in datagrid. So, If you are using datagrid, you can have binding columns and set their values to two columns (done and undone) apart from other columns that you may be binding.You can get this result set in dataset or use executereader to bind the datagrid. Hope, it helps you to understand.

thalz142
Starting Member

7 Posts

Posted - 2008-01-29 : 02:13:40

OWh thx sunil..
Store Procedure?I've read about it but never used it since didnt understand.I will read more to get information.Thanx again

there many operation..ist posible to calculate percentage?
Let say i got stat 'Done' = 2, 'UnDone' = 2
So the total = 4.
If i want column to calculate percentage = 'Done'.Is't posible
to get another column = '50%' ?
Is sql server support that operation?

Owh thx again!Any help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 02:24:08

[code]SELECT field1Company,
[Done] = SUM(CASE WHEN field2Status = 'Done' THEN 1 ELSE 0 END),
[Uncomplete] = SUM(CASE WHEN field2Status = 'Uncomplete' THEN 1 ELSE 0 END),
[Done %] = SUM(CASE WHEN field2Status = 'Done' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
[Uncomplete %] = SUM(CASE WHEN field2Status = 'Uncomplete' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
FROM COMPANYTABLE
GROUP BY field1Company[/code]

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

thalz142
Starting Member

7 Posts

Posted - 2008-01-29 : 02:39:07

thx khtan and sunil..u guys help me alot..

so i can close my topic now...thx again.

thalz142
Starting Member

7 Posts

Posted - 2008-01-30 : 21:18:06

Hello again..
I wanna ask..is't posible to generate a bar chart from all my query with asp.net?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-30 : 21:34:37

oh that's out of my knowledge scope. Maybe someone else can help.

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

thalz142
Starting Member

7 Posts

Posted - 2008-01-31 : 02:42:21

Ok thx khtan

aaa im' stuck again...
How to devide my query let say i want my data list all from Jan08 to Mac08.I want it devide/group by month and company..but in db all data not im the same date.So i ve to select where [date range]..
So that i can see on Jan08,feb08 and mac08 group by company for each month...

Any idea?thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources