Help adding field to results - 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 2008 Forums

Transact-SQL (2008)

Help adding field to results

Author

Topic

cardinalsfan
Starting Member

2 Posts

Posted - 2012-04-11 : 10:12:32

Hey everyone!! I need a little help with something. I use the query below to pull results from a table to let me know how many records I have from each collectorid to build. The problem is that the field we use for collectorid doesn't match the field I use to build with. I need to add a row to the results with some sort of code that will give a result based on the collectorid returned. For example, if the collectorid is INA, I want the new row to show INAC. PAL needs to show PALA and so forth. Any help?

select tr.collectorid, count(*)   
from C3CivilAutomation.dbo.vw_TransactionReceipts as tr (nolock)  
where tr.exportdate = '1900-01-01'  
and tr.collectorid in ('INA','PAL','PMC','AZP','OHF','PAB','IMW','MIW','WCA','OTC','TXT','AZM','MIL',
'MIF','MIJ','MIK','MIM','MIN','OH2','MIS','MIH','OH6','OH1','HCF','AKC','MIE')  
and tr.DocketSession <> 'Migrated from 05'  and tr.Caseheld = '0'    
group by tr.collectorid order by tr.collectorid compute sum(count(*))
OPTION (MAXDOP 1)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-11 : 10:30:16

[code]
select
tr.collectorid,
case tr.collectorid
when 'INA' then 'INAC'
when 'PAL' then 'PALA'
when ...
else '???'
end as NewColumnName,
count(*)
from ...
[/code]

btw. compute will disappear in the next version of sql server... ask google

No, you're never too old to Yak'n'Roll if you're too young to die.