counting for an "id" in rows - 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 2000 Forums

SQL Server Development (2000)

counting for an "id" in rows

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-08 : 05:49:12

i have rows that contains diffrent id number of pepole lik this
234645,234823456,234,12378 01/01/2000
234645,23999,2984534,12378 01/01/2000
where in each row no id appears more then once!
and so on
i need to do 2 things :
1)to get a list of all the companies in the rows and the number of times they appear.
2)to do the same but with a time limit (which i think after (1) is solvded this will be easy
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pootle_flump

1064 Posts

Posted - 2006-05-08 : 06:03:44

Hi

This would be trivial in the extreme if the data was normalised:
http://r937.com/relational.html
Is there any reason it isn't or cannot be made to be so? If not the solution will be convoluted and inefficient. I wouldn't fancy writing it for starters

HTH

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-08 : 06:24:46

i agree i shoulb be normalised
but i have recived it like this so i cant change it now its very vry complicated to do so :(
so how can isolve this?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-08 : 08:24:53

Apply this
http://www.nigelrivett.net/SQLTsql/f_GetEntryDelimiitted.html

then
select id, c = dbo.f_GetEntryDelimiitted(fld, 1, ',', 'N')
union all
select id, dbo.f_GetEntryDelimiitted(fld, 2, ',', 'N')
union all
select id, dbo.f_GetEntryDelimiitted(fld, 3, ',', 'N')
...

up to the max will give you a normalised structure then

select c, count(*)
from
(
select id, c = dbo.f_GetEntryDelimiitted(fld, 1, ',', 'N')
union all
select id, dbo.f_GetEntryDelimiitted(fld, 2, ',', 'N')
union all
select id, dbo.f_GetEntryDelimiitted(fld, 3, ',', 'N')
...
) a
group by c

I think this might come up against the bug with using a function to form a derived table but give it a go.
It won't be quick.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources