How can i avoid all the repetitive select statment

sqlmega 2014-11-13 16:02:40

I trying to make one select statment which does all the work rather than multiple select statements

/storage/attachments/1066-4753342685.queryanalysis

Mart 2014-11-14 13:32:16

Hi SQLMega

I started playing with this but have run out of time. Here's what I have anyway, hopefully you can use this to get you going – I could of course be on completely the wrong track 🙂

declare @Statement nvarchar(max) = ''
 
SELECT @Statement += 'SELECT ' + Column_Name + char(10) +
                 'FROM dxtest' + char (10) +
                 'UNION' + char(10) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name like 'dx%'
--AND
--add appropriate filters here for table and schema
 
 
PRINT @Statement
 
--write it with your original query...
                SELECT DISTINCT
                diag.Code, diag.Description 
                FROM     
                  udfAtnSplitListReturnString(@DiagnosisCodeList,',') temp
                  INNER JOIN dbo.tblHPPDiagnosisCode diag on temp.Item = diag.Code
                  -- ...here
                  INNER JOIN (SELECT dx1 as DXCode
                             FROM dxtest
                             UNION
                             SELECT dx2
                             FROM dxtest
                             UNION
                             SELECT dx3
                             FROM dxtest
                             ) dx
                             ON dx.DXCode = diag.Code

November 13, 2014

No Comments