How to PIVOT a column by concatenating strings into Sqlserver

Question

Hello, I’m making a query in a database to identify all the columns that are primary key's of the tables of a database and also identify whether or not 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, having the result below:

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

The only way I know how to fix this would be with a PIVOT, the problem is that I do not work very well with the same.

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

Answer 1

As of 2017 version of SQL Server you can use the function STRING_AGG that allows grouping and concatenating 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 earlier 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