Join - like - 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 2005 Forums

Transact-SQL (2005)

Join - like

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 17:02:57

I would like to do a count on the result table below:

declare @CharacteristicsSecurityTypeNull table
(Characteristic varchar(50))

insert @CharacteristicsSecurityTypeNull

select 'ABCP'
UNION ALL
select 'Bear Bonds'
UNION ALL
select 'Bull Bonds'
UNIN ALL
select 'Callable Bonds'
UNION ALL
select 'Capital'

declare @Imports table
(ProfileID int, Characteristic varchar(50))

insert @Imports
select 1, 'Bear Bonds'
UNIN ALL
select 2, 'ABCP'
UNION ALL
select 3, 'CapitalABCP'
UNION ALL
select 4, 'Callable Bonds'
UNION ALL
select 5, 'Callable BondsBear Bonds'

QUESTION:
How do I produce this result table so that I can do a count on Characteristic field?
Thanks

ProfileID Characteristic
1 Bear Bonds
2 ABCP
3 Capital
3 ABCP
4 Callable Bonds
5 Callable Bonds
5 Bear Bonds

I am basically trying to do a count on Characteristic field, so I thought if I produce the result table then I can do a count on it using group by characteristic field. This is what I tried first but it is not correct. I think the issue is to do with the where clause

select
csNull.Characteristic,
Count(*)
from
@Imports i inner join @CharacteristicsSecurityTypeNull csNull
on ltrim(rtrim(i.Characteristic)) = ltrim(rtrim(csNull.Characteristic))
where
--ltrim(rtrim(i.Characteristic)) = 'Credit Facilities'
ltrim(rtrim(csNull.Characteristic)) like '%' + ltrim(rtrim(i.Characteristic)) + '%'
group by
csNull.Characteristic
order by
csNull.Characteristic

Any thoughts please?
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 17:19:04

[code]SELECT
i.ProfileID,
csNull.Characteristic
FROM
@CharacteristicsSecurityTypeNull csNull
INNER JOIN
@Imports i
ON i.Characteristic LIKE '%' + csNull.Characteristic + '%'[/code]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 17:38:22

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources