Sql-server – Select Distinct for Multiple columns

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id 
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id, 
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b 
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;

Currently there is no syntax directly supporting what you are trying to do. As you probably know, names cannot be parametrised in a SQL statement. That means that when you need to substitute names from column values of another table, you have to use dynamic SQL: first build the query string and then execute it. There is just no working around using dynamic SQL in such cases. Furthermore, you have already established for yourself that you cannot use dynamic SQL in a function. So there you are, seemingly stumped.

However, if you insist on using a single SELECT statement for this, there is one way – provided you agree to bend over backwards slightly to achieve the goal, that is. And accept a major limitation of the method.

The solution involves creation of a loopback linked server and using the OPENQUERY function. But first you will need to make sure your dynamic SQL solution works as it is. For the purpose of this answer, I am going to assume that the dynamic SQL looks like this:

DECLARE @sql nvarchar (max) = '', @sqltemplate nvarchar(max) =
'UNION ALL
SELECT
  Column_Name = ''{Column_Name}'',
  Table_Name  = ''{Table_Name}'',
  Max_Length  = MAX(LEN([{Column_Name}]))
FROM
  [oil stop].dbo.[{Table_Name}]
';
SELECT
  @sql += REPLACE(
            REPLACE(
              @sqltemplate,
              '{Column_Name}',
              Column_Name
            ),
            '{Table_Name}',
            Table_Name
          )
FROM
  tempdb.dbo.YourMetaDataTable
;
SET @sql = STUFF(@sql, 1, 9, '');  -- remove the leading UNION ALL
EXECUTE sp_executesql @sql;

Once you have verified the script is working, and made sure the loopback linked server is created, just put the script inside the OPENQUERY function like this:

SELECT
  *
FROM
  OPENQUERY(
    YourLinkedServerName,
    '...'  -- the dynamic SQL script
  )
;

Remember to double each quotation mark (apostrophe) inside the script.

One other important change you will likely need to make is to add a WITH RESULT SETS clause to the EXECUTE statement to describe the result set, so that OPENQUERY can process the output correctly for you. When describing the result set, you will likely just repeat the same type for Column_Name and Table_Name as defined for them in the metadata table. For the example below I am assuming the type to be sysname in both cases. And as for the Max_Length column, I believe int would work well there. So, the modified EXECUTE statement would look like this:

EXECUTE sp_executesql @sql
WITH RESULT SETS
(
  (Column_Name sysname, Table_Name sysname, Max_Length int)
);

For completeness, and to make the lack of elegance in this solution more evident for the wider audience, this is what the final query would look like:

SELECT
  *
FROM
  OPENQUERY(
    [OIL STOP],
    'DECLARE @sql nvarchar (max) = '''', @sqltemplate nvarchar(max) =
    ''UNION ALL
    SELECT
      Column_Name = ''''{Column_Name}'''',
      Table_Name  = ''''{Table_Name}'''',
      Max_Length  = MAX(LEN([{Column_Name}]))
    FROM
      [oil stop].dbo.[{Table_Name}]
    '';
    SELECT
      @sql += REPLACE(
                REPLACE(
                  @sqltemplate,
                  ''{Column_Name}'',
                  Column_Name
                ),
                ''{Table_Name}'',
                Table_Name
              )
    FROM
      tempdb.dbo.MetaData
    ;
    SET @sql = STUFF(@sql, 1, 9, '''');  -- remove the leading UNION ALL
    EXECUTE sp_executesql @sql
    WITH RESULT SETS
    (
      (Column_Name sysname, Table_Name sysname, Max_Length int)
    );
    '
  )
;

The main problem, though, is that the query above still cannot be parametrised, and that is the principal limitation I was talking about. Even though the OPENQUERY script is specified as a string literal, it can only be a single string literal – not a variable, not a complex expression. That means that if you want to apply the query to a different subset of rows of the metadata table, you will have to use a new script for that.