Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is a Dynamic SQL solution possible in this case ? Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 16, 2014 1:23 PM
Points: 105, Visits: 194
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.
Post #1522073
Posted Wednesday, December 11, 2013 3:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:48 PM
Points: 3,636, Visits: 8,151
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1522078
Posted Wednesday, December 11, 2013 5:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:26 PM
Points: 3,420, Visits: 5,350
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522107
Posted Monday, December 16, 2013 11:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 16, 2014 1:23 PM
Points: 105, Visits: 194
Sorry about the late response but this works for what I am trying to do. I will try and expand on it. Thanks guys
Post #1523386
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse