Choosing the right data from a table using SQL

Question

Lets say I have this table name "Stuff"

╔════╦═════╗
║ ID ║ VAL ║
╠════╬═════╣
║  1 ║ A   ║
║  2 ║ B   ║
║  3 ║ C   ║
║  4 ║ C   ║
║  5 ║ C   ║
║  6 ║ D   ║
║  7 ║ B   ║
║  8 ║ E   ║
╚════╩═════╝

I would like a query which can give me an output of where in the numbers the letters are repeating. Feel free to use any column name for the table.

Basically I want something that says "C" is reapeating on number 3, 4, and 5. "B" is repeating on number 2, and 7.

On my work I have thousand of rows. The example I gave you is very granular. If you need more information please let me know.

What is your expected output? For example if you sort by val theb all the records with the same val will be grouped together so it will be easy to see what IDs each one of them relates to. I'm guessing this is not what you want. So what do you? — Andrew Savinykh, Jun 27 '13 at 04:17

Himanshu · Answer 1 · 2013-06-27T05:02:21.577

3

You can try this one:

SELECT Val, Id =
    STUFF((SELECT ' , ' + CAST( Id as varchar(20))
           FROM Stuff b 
           WHERE b.Val = a.Val 
          FOR XML PATH('')), 1, 2, '')
FROM Stuff a
GROUP BY Val;

See this SQLFiddle

Output:

╔═════╦════════════╗
║ VAL ║     ID     ║
╠═════╬════════════╣
║ A   ║  1         ║
║ B   ║  2 , 7     ║
║ C   ║  3 , 4 , 5 ║
║ D   ║  6         ║
║ E   ║  8         ║
╚═════╩════════════╝

edited Jun 27 '13 at 05:02

answered Jun 27 '13 at 03:45

Himanshu

31,810
31
111
133

1

Yep, this, and all the rest of techniques from here: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ – Andrew Savinykh Jun 27 '13 at 04:20
@zespri - Nice link. Thanks. – Himanshu Jun 27 '13 at 04:22
1

@hims056 - In your query DISTINCT with GROUP BY is unnecessary. – Devart Jun 27 '13 at 04:56
@Devart - Yeah. Corrected. Didn't notice that. Thanks. – Himanshu Jun 27 '13 at 05:02
1

+1 - for the good answer. – Devart Jun 27 '13 at 05:03

score 3 · Answer 2 · answered Jun 27 '13 at 04:54

Try this one -

DECLARE @temp TABLE (Id INT, Val CHAR(1))
INSERT INTO @temp (Id, Val)
VALUES
    (1, 'A'),(2, 'B'),(3, 'C'),
    (4, 'C'),(5, 'C'),(6, 'D'),
    (7, 'B'),(8, 'E')

SELECT
      Val
    , RepeatValues = STUFF((
          SELECT ', ' + CAST(ID AS VARCHAR(10))
          FROM @temp a
          WHERE a.Val = b.Val
          FOR XML PATH ('')), 1, 2, '')
FROM @temp b
GROUP BY Val

score 2 · Answer 3 · edited Jun 27 '13 at 04:50

2

try like this...

SELECT
      name
    , STUFF((
          SELECT ',' + CAST(ID AS VARCHAR(MAX))
          FROM [stuff] a
          WHERE a.name = b.name
          FOR XML PATH ('')), 1, 1, '') AS RepeatIds
FROM [stuff] b
GROUP BY name

edited Jun 27 '13 at 04:50

Devart

119,203
23
166
186

answered Jun 27 '13 at 03:45

Amit Singh

8,039
20
29

+1 - for the good answer. – Devart Jun 27 '13 at 04:50
@Devart thank's!!!!!! and many thanks for making my answer look gud....thanks fr your edit too... – Amit Singh Jun 27 '13 at 04:53
Also preferably use `VARCHAR()` instead of `VARCHAR(MAX)`. And you're welcome @AmitSingh :). – Devart Jun 27 '13 at 05:00
@Devart i m not sure of length of given data type...so thats y i taken as max...does it make an impact?? – Amit Singh Jun 27 '13 at 05:03
It's not critical, but in your case allocated more memory than you really need. – Devart Jun 27 '13 at 05:07

Choosing the right data from a table using SQL

3 Answers3

See this SQLFiddle