Help with query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with query

Author

Topic

learntsql

524 Posts

Posted - 2010-04-22 : 08:07:18

declare @EventData table(PersonID int,EventID varchar(50),ParticipationCount int)

insert into @EventData
select 1,'E1',1
union all
select 1,'E2',2
union all
select 1,'E3',3
union all
select 2,'E1',1
union all
select 2,'E2',2
union all
select 3,'E1',1
union all
select 3,'E3',2

--select * from @EventData

--OutPut should look like this
PersonID|E1|E2|E3|TotalParticipationCount
1 |0 |0 |1 | 2(E1+E2)
2 |1 |1 |0 | 1(E1)
3 |0 |0 |1 | 1(E1)

The Above @EventData table maitains Events information.
Here the person to complete the course he has to first Participate the
Event1 (E1),E2(optional) and finally must complete the E3.
In TotalParticipationCount column we show count of previous events(E1 and E2).
for e.g;P1 Participated all the Events thus we showed 2 under TotalParticipationCount(E1+E2)
P2 didnt complete all his events

Please guide me.
TIA.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-22 : 08:29:08

do it in the front end. This is really easy to do by looping over the columns in any application.

If you must do it in the database. there are ton's of posts here about PIVOT and DYNAMIC CROSSTAB -- you should search for those first.

Good luck,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:32:54

are the events always fixed (E1,E2,E3) ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2010-04-22 : 08:43:06

Thanks for The reply,
Yes the Events are fixed.
I want generate a report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:50:10

then just do like


SELECT PersonID,
SUM(CASE WHEN EventID ='E1' THEN 1 ELSE 0 END) AS E1,
SUM(CASE WHEN EventID ='E2' THEN 1 ELSE 0 END) AS E2,
SUM(CASE WHEN EventID ='E3' THEN 1 ELSE 0 END) AS E3,
COUNT(EventID) AS Total
FROM Table
GROUP BY PersonID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2010-04-22 : 09:51:51

Thanks Visakh,
In total column we should not sum all the events.
for e.g;P1 Participated all the Events thus we showed 2 under TotalParticipationCount(E1+E2),we shouldnt include E3 in to total count

learntsql

524 Posts

Posted - 2010-04-23 : 00:42:30

Any Ideas please...!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 06:02:00

What is the logic behind this output?

PersonID|E1|E2|E3|TotalParticipationCount
1 |0 |0 |1 | 2(E1+E2)
2 |1 |1 |0 | 1(E1)
3 |0 |0 |1 | 1(E1)

E1 is 0 for PersonId 1 and also 0 for Personid 3

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:13:23

quote:
Originally posted by learntsql

Thanks Visakh,
In total column we should not sum all the events.
for e.g;P1 Participated all the Events thus we showed 2 under TotalParticipationCount(E1+E2),we shouldnt include E3 in to total count

can you explain your original output properly plzz?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources