Is a Dynamic SQL solution possible in this case ?

Question

Is a Dynamic SQL solution possible in this case ?

sqlislife

Ten Centuries

Points: 1251
More actions
December 11, 2013 at 2:57 pm

#395976

Hey Guys,
I have a table with the following data.
Create Table #Temp
(ID INT, MYID INT, TITLE VARCHAR(20))
INSERT INTO #Temp
Select 1, 1, 'First' UNION ALL
Select 1, 2, 'Second' UNION ALL
Select 1, 3, 'Third' UNION ALL
Select 1, 4, 'Fourth' UNION ALL
Select 1, 5, 'Fifth' UNION ALL
Select 1, 6, 'Sixth' UNION ALL
Select 1, 7, 'Seventh' UNION ALL
Select 1, 8, 'Eighth' UNION ALL
Select 1, 9, 'Ninth' UNION ALL
Select 1, 10, 'Tenth' UNION ALL
Select 2, 3, 'Third' UNION ALL
Select 2, 4, 'Fourth' UNION ALL
Select 2, 5, 'Fifth' UNION ALL
Select 2, 6, 'Sixth' UNION ALL
Select 2, 7, 'Seventh' UNION ALL
Select 2, 8, 'Eighth' UNION ALL
Select 2, 9, 'Ninth' UNION ALL
Select 3, 1, 'First' UNION ALL
Select 3, 2, 'Second' UNION ALL
Select 3, 3, 'Third' UNION ALL
Select 3, 4, 'Fourth' UNION ALL
Select 3, 5, 'Fifth' UNION ALL
Select 3, 6, 'Sixth' UNION ALL
Select 3, 7, 'Seventh' UNION ALL
Select 3, 8, 'Eighth'
Select * from #Temp
I am trying to get an intersect of each ID value. The result should look something like this ;
Create Table #TempIntersect
(MYID INT, TITLE VARCHAR(20))
INSERT INTO #TempIntersect
Select MYID, TITLE from #temp
where id = 1
intersect
Select MYID, TITLE from #temp
where id = 2
intersect
Select MYID, TITLE from #temp
where id = 3
Select * from #TempIntersect
Here are my questions,
1. Is there any other method more optimal than the intersect function ? It seems to be the simplest way to achieve this but I know it's rarely used.
2. This is the main question, in the future, there will be more IDs added to this table, how can this be dynamically re-written so that I don't have to modify it
everytime an ID has been added ? The ideal solution will dynamically create all the intersect statements based on the number of IDs.
For example, if we added the following into the #Temp table
INSERT INTO #TEMP
Select 4, 4, 'Fourth' UNION ALL
Select 4, 5, 'Fifth' UNION ALL
Select 4, 6, 'Sixth' UNION ALL
Select 4, 7, 'Seventh' UNION ALL
Select 4, 8, 'Eighth' UNION ALL
Select 4, 9, 'Ninth'
The query should somehow be able to dynamically create a fourth select statement without me going to modify it. Something like this;
Select MYID, TITLE from #temp
where id = 1
intersect
Select MYID, TITLE from #temp
where id = 2
intersect
Select MYID, TITLE from #temp
where id = 3
intersect
Select MYID, TITLE from #temp
where id = 4
Thanks guys.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

There's no need to use dynamic code. Here's one option and maybe someone could give a better one because I'm not so sure on its performance with big loads. It's quite simple though. 🙂

SELECT MYID,

TITLE

FROM #Temp

GROUP BY MYID,

TITLE

HAVING COUNT(*) = (SELECT COUNT( DISTINCT ID) FROM #Temp)

ORDER BY MYID,

TITLE

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Luis Cazares (12/11/2013)
There's no need to use dynamic code. Here's one option and maybe someone could give a better one because I'm not so sure on its performance with big loads. It's quite simple though. 🙂
SELECT MYID,
TITLE
FROM #Temp
GROUP BY MYID,
TITLE
HAVING COUNT(*) = (SELECT COUNT( DISTINCT ID) FROM #Temp)
ORDER BY MYID,
TITLE

+1

Unless of course you allow duplicates in your table, like the last row in the following:

INSERT INTO #Temp

Select 1, 1, 'First' UNION ALL

Select 1, 2, 'Second' UNION ALL

Select 1, 3, 'Third' UNION ALL

Select 1, 4, 'Fourth' UNION ALL

Select 1, 5, 'Fifth' UNION ALL

Select 1, 6, 'Sixth' UNION ALL

Select 1, 7, 'Seventh' UNION ALL

Select 1, 8, 'Eighth' UNION ALL

Select 1, 9, 'Ninth' UNION ALL

Select 1, 10, 'Tenth' UNION ALL

Select 2, 3, 'Third' UNION ALL

Select 2, 4, 'Fourth' UNION ALL

Select 2, 5, 'Fifth' UNION ALL

Select 2, 6, 'Sixth' UNION ALL

Select 2, 7, 'Seventh' UNION ALL

Select 2, 8, 'Eighth' UNION ALL

Select 2, 9, 'Ninth' UNION ALL

Select 3, 1, 'First' UNION ALL

Select 3, 2, 'Second' UNION ALL

Select 3, 3, 'Third' UNION ALL

Select 3, 4, 'Fourth' UNION ALL

Select 3, 5, 'Fifth' UNION ALL

Select 3, 6, 'Sixth' UNION ALL

Select 3, 7, 'Seventh' UNION ALL

Select 3, 8, 'Eighth' UNION ALL

Select 3, 8, 'Eighth';

I believe what Luis has done could be modified for this case, but I'll leave that to you if you say it can happen.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sqlislife Ten Centuries Points: 1251 More actions · Answer 3

Sorry about the late response but this works for what I am trying to do. I will try and expand on it. Thanks guys