Same query but for 200 + values

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

General SQL Server Forums

New to SQL Server Programming

Same query but for 200 + values

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-08-09 : 07:10:58

Hi all,

The code below runs for just one study in the 'Where' clause (from the tblCOMstudy.[0020000D] column), I have 200+ studies I'd like to run in the same query.

Also, I'd like to remove the Select * and only return values from the numeric column 00818, however when I swap them ALL the values returned are 00818, which is obviously incorrect. So, each output column needs to be prefaced by the study number.

SELECT * --(needs to be ColumnName 00818)
FROM dbo.tblCOMImage
INNER JOIN dbo.tblCOMSeries ON dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2
INNER JOIN dbo.tblCOMStudy ON dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1
INNER JOIN dbo.tblFile ON dbo.tblCOMImage._idFile = dbo.tblFile.idFile
Where dbo.tblCOMstudy.[0020000D] = '1.2.840.113619.2.55.3.2383071842.625.1292590480.81'
--(but need to run the whole statement against 200 more!!)

Cheers,

JB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-09 : 07:15:52

Do you have some examples? Which ComStudy belongs to which ColumnName?

N 56°04'39.26"
E 12°55'05.63"

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-08-09 : 07:36:01

Sure, here are some examples of ComStudy Numbers, hopefully each study number can become the columname in the output...

1.2.124.113532.10.142.98.12.20070611.134228.514127
1.2.840.113619.2.55.3.2383071842.114.1269349892.866
1.2.840.113619.2.55.3.2383071842.946.1287143514.156
1.2.840.113619.2.55.1.1762896980.2275.1233146884.287
1.2.124.113532.10.142.98.12.20091008.124255.1822279
1.2.840.113619.2.55.1.1762896980.2176.1226062664.742
1.2.840.113619.2.55.1.1762896980.2325.1197377361.539
1.2.840.113619.2.55.3.2383071842.625.1292590480.81
1.2.840.113619.2.94.1163581648543.0207930102215.33771.138536
1.2.840.113619.2.55.1.1762896980.2190.1187351789.341
1.2.840.113619.2.55.1.1762896980.2292.1188560831.81
1.2.840.113619.2.55.1.1762896980.2195.1201179471.815
1.2.840.113619.2.55.1.1762896980.2198.1204894617.342
1.2.840.113619.2.55.1.1762896980.2179.1208951152.697
1.2.840.113619.2.55.1.1762896980.2183.1245325491.812
1.2.840.113619.2.55.3.2383071842.588.1314792954.333
1.2.124.113532.10.142.98.12.20100324.93920.2074074
1.2.840.113619.2.94.1162470156840.0207930102215.25698.186358
1.2.840.113619.2.55.1.1762896980.2199.1174394353.708
1.2.840.113619.2.55.1.1762896980.2298.1184934949.751

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-09 : 07:37:36

Then you need dynamic SQL, if you want the resultset to grow in width rather than in length.

N 56°04'39.26"
E 12°55'05.63"

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-08-09 : 08:34:52

The following suffices to return multiple studies, but not sure how to group by each studynumber....

SELECT * --(needs to be ColumnName 00818)
FROM dbo.tblCOMImage
INNER JOIN dbo.tblCOMSeries ON dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2
INNER JOIN dbo.tblCOMStudy ON dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1
INNER JOIN dbo.tblFile ON dbo.tblCOMImage._idFile = dbo.tblFile.idFile
Where dbo.tblCOMstudy.[0020000D] in ('1.2.840.113619.2.55.3.2383071842.625.1292590480.81', '1.2.840.113619.2.55.3.2383071842.114.1269349892.866')
--(but need to run the whole statement against 200 more!!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources