improving the search for an alias column

Question

Look at this SQL code written by Linoff:

approach used by Taryn for SQL concatenation

SELECT a.*, 
       (SELECT STUFF( (SELECT DISTINCT '; ' + r.[description]
                       FROM [tdt_AD_Teste] r
                       WHERE a.pid = r.pid AND
                             r.[displayname] = 'member'
                       FOR XML PATH('')
                      ), 1, 2, ''
                    )
       ) Descript
FROM (SELECT a.pid,
             MAX(CASE WHEN a.displayname = 'name' then [group] end) as name,
             MAX(CASE WHEN a.displayname = 'sd' then [group] end) as sd,
             MAX(CASE WHEN a.displayname = 'description' then [group] end) as description
      FROM tdt_AD_Teste a
      GROUP BY a.pid
     ) a;

This code will generate 5 columns: pid ; name; sd; description; descript

I want to make a search for the Descript part (it is an alias)...

if we write as:

where [Descript] like '%' + '<any_value>'+ '%'

it will throw an invalid column name 'Descript' (looking for pid, sd, description and name is easy).

If I write the same code as you see above... WITH A WRAP select * from (code) b where (...)

SELECT * 
FROM 
   (
   SELECT a.*, 
           (SELECT STUFF( (SELECT DISTINCT '; ' + r.[description]
                           FROM [tdt_AD_Teste] r
                           WHERE a.pid = r.pid AND
                                 r.[displayname] = 'member'
                           FOR XML PATH('')
                          ), 1, 2, ''
                        )
           ) Descript
    FROM (SELECT a.pid,
                 MAX(CASE WHEN a.displayname = 'name' then [group] end) as name,
                 MAX(CASE WHEN a.displayname = 'sd' then [group] end) as sd,
                 MAX(CASE WHEN a.displayname = 'description' then [group] end) as description
          FROM tdt_AD_Teste a
          GROUP BY a.pid
         ) a ) b
 WHERE b.Descript like '%' + '<any_value> + '%' ;

it works but it takes too much time to find some string.... (it can take around20 s...). Is there a faster way to do the search for this case?

Filter the data *before* trying to concatenate the strings. In SQL Server 2017 you can use STRING_AGG to aggregate strings, once you've filtered the data — Panagiotis Kanavos, Jul 02 '18 at 11:12
I do not have sql2017. how can I do it in that way? just curiosity.. — graphene, Jul 02 '18 at 12:59

score 2 · Answer 1 · answered Jul 02 '18 at 11:09

2

You don't have to generate the string -- assuming you are looking for a description that is in one row. You can do:

SELECT a.pid,
       MAX(CASE WHEN a.displayname = 'name' then [group] end) as name,
       MAX(CASE WHEN a.displayname = 'sd' then [group] end) as sd,
       MAX(CASE WHEN a.displayname = 'description' then [group] end) as description
FROM tdt_AD_Teste a
GROUP BY a.pid
HAVING SUM(CASE WHEN description LIKE '%' + <any value> + '%' THEN 1 ELSE 0 END) > 0;

You can use this in the subquery in the FROM and then concatenate the descriptions in the outer query, you also need that.

answered Jul 02 '18 at 11:09

Gordon Linoff

1,242,037
58
646
786

I need to have the generation of string. I need both (concatenation string and search) at the same time. The suggestion of Panagiotis is great but I do have sql 2012... I will stick to the way I did shown above. The worst scenario for a search was 20 s. In general it takes 3 s - fast.. also the typical SQL order for execution of some clauses is: FROM ON JOIN WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY it is the descript part that I need to filter, not the description. – graphene Jul 02 '18 at 13:22
@graphene . . . As I tried to explain in the answer, you can just plug this into the subquery and use the same outer query. – Gordon Linoff Jul 02 '18 at 14:34

improving the search for an alias column

1 Answers1