Need help with query

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

select User

, SuM(Case when DocType ='Type1' then 1 ELSE 0 END) as Type1

, SuM(Case when DocType ='Type2' then 1 ELSE 0 END) as Type2

, SuM(Case when DocType ='Type3' then 1 ELSE 0 END) as Type3

FROM

SourceTable

GROUP By User

* Noel

Nuno SSC-Addicted Points: 428 More actions · Answer 2

Nice!

Thanks noeld for your help.

DocTypes can be added by clients, so I will create a cursor to build a dynamic version of the query you suggested.

Nuno

noeld SSC Guru Points: 96590 More actions · Answer 3

You May Not need a Cursor. If All that is dynamic is just the "Types"

then You could:

Declare @STR varchar(8000)

SET @STR ='Select User'

SELECT @STR = @STR + , 'SuM(Case when DocType =''' + strType +''' then 1 ELSE 0 END) as ' + strType

FROM (

SELECT DISTINCT Cast(Type as varchar) as strType

FROM TypeSource

) Q

Set @STR = @STR +' FROM SourceTable GROUP By User '

Exec (@str)

* Noel

Nuno SSC-Addicted Points: 428 More actions · Answer 4

I really have to visit this forum more often!

Thanks a lot!

noeld SSC Guru Points: 96590 More actions · Answer 5

SSC Guru

Points: 96590

February 27, 2004 at 1:20 pm

You're WELCOME

* Noel