CODEWITHSUNDEEP
sql-serverselect
skerdreux
skerdreux

Reputation: 25

How to select only one column

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 ?

Upvotes: 0

Views: 2577

Answers (2)

user11380812
user11380812

Reputation:

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.

enter image description here

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+.

Upvotes: 2

Martin
Martin

Reputation: 16433

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

Upvotes: 3

Related Questions