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.
| Author |
Topic |
|
Hassam
Starting Member
1 Post |
 Posted - 2012-03-09 : 09:03:43
|
| In Query below I want to get multiple records from ImportJob table. In the result set I need a comma separated string of all the distinct JobIDs in to a new column from ImportJob grouped according to [EPName]select [EPName]= Finfo.DropSiteNameWithState + ' ' + Finfo.PhysicalZipPlus4FirstFiveDigits) COUNT(distinct csm.fkJobID) FoundinJobs, (Select COUNT(*) from ContainerSummaryRecord Pallets where Pallets.csmContainerType in( 'P', '1', 'U', 'W', 'Z', 'H', 'A', 'G', 'D', 'R', 'C', 'AB', 'M' ) and Pallets.csmEntryPointActualOrDeliveryLocaleKey = csm.csmEntryPointActualOrDeliveryLocaleKey)as QualifiedPallets, (Select COUNT(*) from ContainerSummaryRecord Containers where Containers.csmEntryPointActualOrDeliveryLocaleKey = csm.csmEntryPointActualOrDeliveryLocaleKey)as Containers, (Select COUNT(*) from ContainerSummaryRecord UserPallets where UserPallets.csmContainerType = 'Z' and UserPallets.csmEntryPointActualOrDeliveryLocaleKey = csm.csmEntryPointActualOrDeliveryLocaleKey)as UserPallets, SUM(csm.csmNumberofCopies) TotalCopies, SUM(csm.csmTotalWeightproductonly) TotalWeight from ImportJob imj inner join SegmentRecord seg on imj.JobID = seg.fkJobID inner join ContainerSummaryRecord csm on csm.fkJobID = seg.fkJobID and csm.fkSegmentID = seg.segSegmentID left join FacilityInfo FInfo on csm.csmEntryPointActualOrDeliveryLocaleKey = FInfo.LocaleKeyWithLOCSuffix where ( imj.fkBranchID = @fkBranchID ) and ( ( @IncludeJobs = 'Own' AND imj.fkPartnerID IS NULL ) OR ( @IncludeJobs = 'Partner' AND imj.fkPartnerID IS NOT NULL ) OR ( @IncludeJobs = 'All' ) )group by seg.segPrincipalProcessingCategory, csm.csmEntryPointActualOrDeliveryLocaleKey, Finfo.DropSiteNameWithState, Finfo.PhysicalZipPlus4FirstFiveDigits having seg.segPrincipalProcessingCategory = @MailShape |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Concatenation