How to select only one column

Question

I think it's a stupid question but I did not find any answer. I have a very simple select query like this :

select [column_name], [table_name] from [sde].[SDE_column_registry]
where [table_name] like 'TESTFEATURECLASS' and [column_name] <> 'SHAPE'

and the answer is :

column_name         table_name
-----------         ------------
GDB_GEOMATTR_DATA   TESTFEATURECLASS
myFieldText         TESTFEATURECLASS
OBJECTID            TESTFEATURECLASS

but I want only the first column to concatenate into one line :

GDB_GEOMATTR_DATA, myFieldText, OBJECTID

How can I do all of this "easy" stuff ?

So many duplicates: https://stackoverflow.com/questions/54334/how-do-i-concatenate-text-in-a-query-in-sql-server/5706, https://stackoverflow.com/questions/30011871/how-to-concatenate-in-sql-server, https://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into — Chris Steele, Apr 25 '19 at 21:02

score 3 · Accepted Answer · answered Apr 25 '19 at 16:10

If you are using SQL Server 2017+ you could use STRING_AGG:

SELECT  STRING_AGG([column_name], ',')
  FROM  [sde].[SDE_column_registry]
  WHERE [table_name] LIKE 'TESTFEATURECLASS' AND
        [column_name] <> 'SHAPE'

This will output:

GDB_GEOMATTR_DATA, myFieldText, OBJECTID

score 2 · Answer 2 · answered Apr 25 '19 at 19:11

You can combine STUFF with XML PATH. For example if we apply you request to AdventureWorks database and let's say we would like to display columns for tables that contains 'Person' in their name except the column 'BusinessEntityId' we could use following T-SQL snippet.

SELECT t1.name, 
(
    SELECT STUFF(C.name, 1, 0, '') + ' '
    FROM sys.columns c
         INNER JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.object_id = t1.object_id AND c.name <> 'BusinessEntityId'
    FOR XML PATH('')
) AS COLUMNLIST
FROM sys.tables t1
WHERE t1.name LIKE '%person%';

Basically, it is the same as in your example. Your example is even simpler. In the AdventureWorks database there are five such tables, and the query result is shown in the image below.

Great resource to explore is Simple Talk article on the following link Concatenating Row Values in Transact-SQL

The solution works fine on SQL Server 2005+. The solution provided by Martin ( it is not my alias ) is preferred if you use SQL Server 2017+.

How to select only one column

2 Answers2