0

I have this data into the testDB on SQL Server:

enter image description here

I want write query to get this data from the table:

1 --------> 3
2 --------> 2

How can I do this?

Thanks. and how can i get this data from up table?

1----->A,B,C
2----->D,E
Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
elnaz irani
  • 277
  • 1
  • 3
  • 11

2 Answers2

0

A simple GROUP BY would do the trick.

SELECT
    columnX,
    COUNT(*)
FROM <YourTable>
GROUP BY columnX

For the other question:

SELECT
    columnX,
    STUFF((
        SELECT ',' + columnY
        FROM YourTable
        WHERE columnX = t.columnX
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')  
    ,1 ,1 , '')
FROM YourTable t
GROUP BY t.columnX
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • thanks for pay attention to my problem my dear friend,i update my question,please review my question.thanks. – elnaz irani May 11 '15 at 06:22
  • i write this:SELECT x, STUFF(( SELECT ',' + y FROM testDB WHERE x = t.y FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') ,1 ,1 , '') FROM testDB t GROUP BY t.x – elnaz irani May 11 '15 at 06:36
  • but i get this error:Msg 8120, Level 16, State 1, Line 6 Column 'testDB.y' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – elnaz irani May 11 '15 at 06:36
  • All the columns contained in your `SELECT` statement should in fact appear in the `GROUP BY` clause, except for those passed as params of an aggregate function (e.g `COUNT(z)`). So your query should work with: `GROUP BY t.x, t.y` – Yannoff May 19 '15 at 12:55
0

or else use Window function in sql server:
Case 1:

 select count(column x) over(partition by column x) as cnt,
    column x,
    column y from table

You can use this FOR XML PATH & STUFF trick for CASE 2: 

SELECT
    b.columnX,
    COUNT(*) as cnt,STUFF( (SELECT ',' + a.column y
                             FROM table a 
                             where a.column x = b.coulmn x
                             FOR XML PATH('')), 
                            1, 1, '')
FROM table b
GROUP BY b.columnX
knkarthick24
  • 3,106
  • 15
  • 21