How to do PIVOT from a column, concatenating strings in SQLServer

Question

How to do PIVOT from a column, concatenating strings in SQLServer

Navigation

#1 by (2 votes)

3

Hello, I am doing a query in a database to identify all the columns that are primary key 's of the tables of a database and also to identify if they are identity . For this I use the query below:

SELECT 
    OBJECT_NAME(C.OBJECT_ID) AS TABLE_NAME, 
    COL_NAME(C.OBJECT_ID,C.column_id) AS COLUMN_NAME,
    COLUMNPROPERTY(C.OBJECT_ID,COL_NAME(C.OBJECT_ID,C.column_id),'IsIdentity') IS_IDENTITY
FROM 
    sys.indexes I
INNER JOIN  sys.index_columns C ON 
    (I.OBJECT_ID = C.OBJECT_ID) AND
    (I.index_id = C.index_id)
WHERE 
    (i.is_primary_key = 1)
ORDER BY
    OBJECT_NAME(C.OBJECT_ID)

My problem is that there are tables in the database that can contain joint primary keys, and the result is as follows:

+------------+-------------+-------------+
| TABLE_NAME | COLUMN_NAME | IS_IDENTITY |
+------------+-------------+-------------+
| Foo        | Id          |           1 |
| Bar        | Id          |           1 |
| Bin        | FooId       |           0 |
| Bin        | BarId       |           0 |
+------------+-------------+-------------+

The only way I know about solving this would be with PIVOT , the problem is that I do not know how to work very well with it.

How do I have all columns that are primary key 's of a table inside the same cell as in an array (comma separated)?

sql sql-server tsql

asked by anonymous 21.08.2018 / 16:21

1 answer

Sass does not compile special character Diff between two commits

score 2 · Accepted Answer

Starting with the 2017 version of SQL Server you can use the STRING_AGG allows you to group and concatenate results:

SELECT x.TABLE_NAME,
       STRING_AGG(x.COLUMN_NAME, ', ') AS COLUMN_NAMES
  FROM (
  -- Sua query
  ) x
 GROUP BY x.TABLE_NAME;

A simple solution for previous versions is to create a function:

CREATE FUNCTION fn_chaves(@object_id INT)
  RETURNS NVARCHAR(MAX)
AS
BEGIN
  DECLARE @texto NVARCHAR(MAX) = '';

  SELECT @texto = @texto + ',' + COL_NAME(C.OBJECT_ID,C.column_id) AS COLUMN_NAME
    FROM sys.indexes I
        INNER JOIN sys.index_columns C ON I.OBJECT_ID = C.OBJECT_ID
                                      AND I.index_id = C.index_id
  WHERE i.is_primary_key = 1
    AND C.OBJECT_ID = @object_id;

  RETURN @texto;
END;

And use as follows:

SELECT t.name,
      fn_chaves(t.object_id) AS chaves
  FROM sys.tables t