SQLTeam.com | Weblogs | Forums

Counting Zipcodes Instead of Sheep

I have the following items: claim #, zip, state.

qqqq 92273 CA
qqqq 92275 CA
rrrr 46157 IN
yyyy 82604 WY
yyyy 82624 WY

I need to see if there are multiple zips for the same state for the same claim. In this case, both the claims for CA & WY meet that criteria. What I want to do is....
.... if there is another zip for the same state, then it gets posted on the previous SAME state line and they should end up looking like this:

qqqq 92273 CA 92275
qqqq 92275 CA ------
rrrr 46157 IN ------
yyyy 82604 WY 82624
yyyy 82624 WY -----

Can anyone show me how to do that?

Thanks!

Oh Great Xerces!

  1. Is this for Microsoft sql server
    2.What if there are 50 rows you want those as 49 extra columns?

For a single "next" value, this is easy if you are on SQL 2012 or later:

CREATE TABLE #claims ( claim# varchar(10) NOT NULL, zip char(5) NOT NULL, state char(2) NOT NULL)
INSERT INTO #claims VALUES
('qqqq','92273','CA'),
('qqqq','92275','CA'),
('rrrr','46157','IN'),
('yyyy','82604','WY'),
('yyyy','82624','WY')

SELECT c.*, LEAD( c.zip, 1, '-----') OVER(PARTITION BY c.claim# 
    ORDER BY c.zip) AS next_zip
FROM #claims c

hi

I made all the zip codes comma seperated values
Is that OK ???

Or you want them as Seperate Columns !!!!

please click arrow to the Left for DROP CREATE sample data script
drop table #sampledata 
go 

create table #sampledata 
(
claim# varchar(100) , zip int, state varchar(100)
)
go 

insert into #sampledata select  'qqqq', 92273 ,'CA'
insert into #sampledata select  'qqqq', 92275 ,'CA'
insert into #sampledata select  'qqqq', 91116 ,'CA'
insert into #sampledata select  'qqqq', 92333 ,'CA'
insert into #sampledata select  'qqqq', 92456 ,'CA'
insert into #sampledata select  'qqqq', 91122 ,'CA'

insert into #sampledata select  'rrrr', 46157 ,'IN'

insert into #sampledata select  'tttt', 46166 ,'PO'

insert into #sampledata select  'yyyy', 82604 ,'WY'
insert into #sampledata select  'yyyy', 82624 ,'WY'
go 

select 'sample data',* from #sampledata
go

image

please click arrow to the left for SQL ...
SELECT  
         'SQL Output' 
        ,E.claim#
        ,E.state
		,STUFF((SELECT  ',' + cast( zip as varchar(10)) FROM #sampledata EE WHERE  EE.claim#=E.claim# AND EE.state = E.state  ORDER BY claim# FOR XML PATH('')), 1, 1, '') AS listStr
FROM 
        #sampledata E
GROUP BY 
        E.claim#
	   ,E.state

image