title: How to concatenate results onto a single row? (TSQL)
body: <p>I found the following link: <a href="https://stackoverflow.com/questions/1339788/sql-how-to-concatenate-results">SQL, How to Concatenate results?</a></p>
  
  <p>However, my use case is slightly different. I have ~64 fields (allowed values <code>Y</code> and <code>N</code>) that are all called "QryGroup" followed by a number. </p>
  
  <p>I've written a query (Query 1) to create another query (Query 2) that will return all the column names where the QryGroup has at least one value = 'Y' within a defined subset.</p>
  
  <p>However, in order to do this, it's creating a large number of unions in order to  get all of the values together. Query 1 is working correctly to create Query 2 in such a way that Query 2 gives me the result I'm looking for, but all in separate lines. I'd like to concatenate these into a single line so that I can put them into Query 3, to only show the columns where at least one of the records in the subset have a 'Y' value in them. </p>
  
  <p>To give an example, I don't want a table with the following columns:</p>
  
  <pre><code>Item Name    Item Description    QryGroup1    QryGroup2    QryGroup3    QryGroup4    QryGroup5    QryGroup6    QryGroup7    QryGroup8
  </code></pre>
  
  <p>when only QryGroup1, 4, and 6 have a non-'N' value. Instead, I want to have </p>
  
  <pre><code>Item Name    Item Description    QryGroup1    QryGroup4    QryGroup6
  </code></pre>
  
  <p>I have the following query that, in this case, would return the values</p>
  
  <pre><code>`QryGroup1`
  `QryGroup4`
  `QryGroup6`
  </code></pre>
  
  <p>Query 2: </p>
  
  <pre><code>select 'QryGroup1' FROM OITM
  WHERE QryGroup1 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup1 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup2' FROM OITM
  WHERE QryGroup2 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup2 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup3' FROM OITM
  WHERE QryGroup3 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup3 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup4' FROM OITM
  WHERE QryGroup4 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup4 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup5' FROM OITM
  WHERE QryGroup5 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup5 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup6' FROM OITM
  WHERE QryGroup6 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup6 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup7' FROM OITM
  WHERE QryGroup7 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup7 HAVING COUNT(*)&lt;&gt; 0 
  union all
  select 'QryGroup8' FROM OITM
  WHERE QryGroup8 &lt;&gt; 'N'  AND oitm.itemcode in ([subset])
  GROUP BY QryGroup8 HAVING COUNT(*)&lt;&gt; 0 
  </code></pre>
  
  <p>I don't think that the link that I posted above will work in my situation because I'm trying to concatenate <code>Union</code>ed results, instead of just normal results. </p>
  
  <p>If you need more clarification, please let me know. I've also included the "Query 1" below, if it helps:</p>
  
  <pre><code>SELECT  replace(replace(replace(stuff(   (select cast('union all select ''' as nvarchar(max)) 
  + column_name + 
  cast(''' FROM OITM' as nvarchar(max))
  + char(13) + 
  cast(' WHERE ' as nvarchar(max))
  + column_name + 
  cast( ' &lt;&gt; ''N''  AND oitm.itemcode in (subset)' as nvarchar(max))
  +char(13) +
  cast(' GROUP BY ' as nvarchar(max)) 
  + column_name   +
  cast(' HAVING COUNT(*)&lt;&gt; 0 ' as nvarchar(max))  
              from information_schema.columns
              where table_schema = 'dbo'
              and   table_name = 'OITM'
              and   column_name like 'QryGroup%%'
              for xml path(''))
          ,1,10,''), '&amp;lt;', '&lt;'), '&amp;gt;', '&gt;'), '&amp;#x0D;', char(13))
  </code></pre>