0

I'm using below query :

    select i.name as 'index name',c.name as 'column' from sys.indexes i 
    join  sys.columns c  on i.object_id = c.object_id;

the query is returning me data like :

    clst    bitpos
    clst    cid
    clst    colguid
    clust   cmprlevel
    clust   fgidfs
    clust   fillfact

However, I want to see my data like:

  clst  bitpos, cid,    colguid
  clust cmprlevel,  fgidfs  fillfact

I tried with distinct and group by like this. But it's not working:

    select distinct(i.name) as 'index name',c.name as 'column' from s 
   ys.indexes i join  sys.columns c  on i.object_id = c.object_id group by i.name;

Could any one please help with this. I'm using mssql

Channa
  • 742
  • 17
  • 28
  • 1
    Hi Shiwangini, your required output can be achieved by following query- SELECT a.name As index_name, column_name = replace ((SELECT c.name AS [data()] from sys.indexes i join sys.columns c on i.object_id = c.object_id WHERE i.name = a.name ORDER BY i.name FOR xml path('')), ' ', ', ') FROM sys.indexes a WHERE a.name IS NOT NULL GROUP BY a.name – Dev Jun 03 '19 at 06:29
  • @Dev , your query is helpful. Thanks – Channa Jun 03 '19 at 06:57

0 Answers0