July 19, 2016 at 11:28 pm
Hi
In my table component information ID is stored as comma separated whenever multiple items are selected from the interface.
Scenario – 1
I would like to search with ComponentInfoID = 1
Result
I should get the Count = 4
Scenario – 2
I would like to search with ComponentInfoID = 1,16
I should get the Count = 1
Can someone help me out
Table :
CREATE TABLE [dbo].[ComponentInfoID](
[ComponentInfoID] [varchar](150) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,2')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,11')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,13,16')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,5,8,9')
Sample Table is created for demo purpose
July 20, 2016 at 12:27 am
At first I thought using DelimitedSplit8K would work for this... but I guess I didn't read the fine print carefully enough.
That will happen at 1:30 in the morning sometimes!
July 20, 2016 at 12:37 am
There are quite few options here and each has it's pros and cons but to recommend any of those I would like to have more information as what may be suitable for a small number of options may not work for a large number etc.
1) How many distinct options do you expect to have?
2) How many of those will each user select (max)?
3) What is the expected cardinality of the search set (user count?)?
4) Any other relevant information you want to share?
😎
July 20, 2016 at 9:52 am
One approach using the DelimitedSplit8K_LEAD[/url]:
DECLARE @searchstring varchar(100) = '2,5';
SELECT searchstring = @searchstring, [count] = COUNT(*)
FROM
(
SELECT c.ComponentInfoID, mx, [count] = COUNT(*)/mx
FROM ComponentInfoID c
CROSS APPLY dbo.DelimitedSplit8K_LEAD(c.ComponentInfoID,',') s
INNER JOIN
(
SELECT item, mx = MAX(ItemNumber) OVER ()
FROM dbo.DelimitedSplit8K_LEAD(@searchstring,',')
) X ON X.Item = s.Item
GROUP BY c.ComponentInfoID, mx
HAVING COUNT(*) >= mx
) XX;
-- Itzik Ben-Gan 2001
July 20, 2016 at 1:03 pm
This should work for you, but your table design is incorrect. In the long term are much better off breaking the various component ID numbers down into separate rows.
D ECLARE @searchstring varchar(100) = '2,5';
WITH cte as (SELECT c.[ComponentInfoID], Item
FROM ComponentInfoID c
CROSS APPLY dbo.DelimitedSplit8K(c.[ComponentInfoID],',') s
)
select ComponentInfoID, count(*)
from cte
where Item in (select item from dbo.DelimitedSplit8K(@searchstring,','))
group by ComponentInfoID
Are you familiar with dbo.DelimitedSplit8k ??
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 20, 2016 at 1:16 pm
The best solution you can have is to normalize your data.
July 20, 2016 at 1:39 pm
Luis Cazares (7/20/2016)
The best solution you can have is to normalize your data.
+10000
It is also the only long term maintainable solution.
OP - when you cram multiple values like that into a single tuple you are violating 1NF. What that means to you is untold amounts of anguish trying to do something that should be really simple (like this query). Sure it might be painful to fix it now but the pain involved is nothing like working with this type of structure in the long term.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2016 at 1:50 pm
I have seen many of such solutions fail, OP, do you want to answer the questions I have asked or even better improve the scheme as suggested?
😎
July 20, 2016 at 2:07 pm
shagil.a.gopinath (7/19/2016)
HiIn my table component information ID is stored as comma separated whenever multiple items are selected from the interface.
Scenario – 1
I would like to search with ComponentInfoID = 1
Result
I should get the Count = 4
Scenario – 2
I would like to search with ComponentInfoID = 1,16
I should get the Count = 1
Can someone help me out
Table :
CREATE TABLE [dbo].[ComponentInfoID](
[ComponentInfoID] [varchar](150) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,2')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,11')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,13,16')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,5,8,9')
Sample Table is created for demo purpose
While I loathe to place a solution for what you are asking for the very reasons explained before my post.....Based on your small dataset and given the constraint of NOT repeating the same number in your Comma Separated Values (i.e. 1,1,2,2 will NOT work for exclusive matching)
SELECT COUNT(1) FROM ComponentInfoID t1 where
t1.ComponentInfoId LIKE '%1%' and t1.ComponentInfoID LIKE '%16%'
SELECT COUNT(1) FROM ComponentInfoID t1 where
t1.ComponentInfoId LIKE '%1%'
/ducks
July 20, 2016 at 2:39 pm
While I loathe to place a solution for what you are asking for the very reasons explained before my post.....Based on your small dataset and given the constraint of NOT repeating the same number in your Comma Separated Values (i.e. 1,1,2,2 will NOT work for exclusive matching)
SELECT COUNT(1) FROM ComponentInfoID t1 where
t1.ComponentInfoId LIKE '%1%' and t1.ComponentInfoID LIKE '%16%'
SELECT COUNT(1) FROM ComponentInfoID t1 where
t1.ComponentInfoId LIKE '%1%'
Thus, the DelimitedSplit solutions.
One thing we ALL agree on is that the database design is poor and should be normalized. When simple questions require complicated solutions, there is usually a flaw in the design.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply