T SQL - Projecting multiple records into one row for each key

Question

A Table can have multiple records for each key as below.

Actual Table:

Key         Value
----------- ---------
2149        805501   
2149        800936   
15385       800622   
18105       997057   
18105       999390

Expected result:

Key         Value
----------- ---------
2149         805501,800936
15385       800622   
18105       997057,999390

I'm unable to think of a solution here. Any help is much appreciated.

You can use a sub select for your value column using FOR XML to comma separate the values. Should your key be 2149 not 219 in the expected result? — David B, Dec 12 '17 at 22:33
Why do you want to do this? It's a SQL anti-pattern. The generally best answer would be "don't". — MatBailie, Dec 12 '17 at 22:36
Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Caius Jard, Dec 12 '17 at 23:23

Hadi · Accepted Answer · 2017-12-12T23:19:30.980

3

You should use FOR XML PATH:

Select Key, STUFF((SELECT ',' + VALUE
                   FROM Table AS T2 
                   WHERE T1.Key = T2.Key
                  FOR XML PATH('')), 1, 1, '') 
FROM Table T1
GROUP BY Key

UPDATE 1

based on your comments

edited Dec 12 '17 at 23:19

answered Dec 12 '17 at 22:42

Hadi

36,233
13
65
124

2

This should be the answer. – David B Dec 12 '17 at 22:43
Thanks a lot, It did work but with a small defect... it is truncating the values after 256 chars.. – BetterLateThanNever Dec 12 '17 at 22:47
@Reddy try it now – Hadi Dec 12 '17 at 22:49
It is returning a char less than previous query – BetterLateThanNever Dec 12 '17 at 22:51
@Reddy try casting as `VARCHAR(8000)` i think this is due to the Varchar default length 255 – Hadi Dec 12 '17 at 22:53
@Reddy i tried it and it is working with and without CASTING !! just copy the value from the field in the result table and paste it on the editor – Hadi Dec 12 '17 at 22:59
Sorry @Hadi i tried it but it is same as shown on SSMS – BetterLateThanNever Dec 12 '17 at 23:03
I'm using SSMS 2008 R2.. Could this a reason? Microsoft SQL Server Management Studio 10.50.4000.0 – BetterLateThanNever Dec 12 '17 at 23:03
@Reddy i am using sql server 2012, i will try backward compatibility and gives you a reply – Hadi Dec 12 '17 at 23:05
@Reddy i tried to set the database compatibility to 2008 and it still working fine – Hadi Dec 12 '17 at 23:09
@reddy try changing the limit from options, check the screenshot i provided, change the XML data limit to Unlimited – Hadi Dec 12 '17 at 23:19
@Hadi Settings on my SSMS matches to yours on the screenshot.. I tried googling but no luck. I really appreciate your interest here. Thanks a lot. – BetterLateThanNever Dec 12 '17 at 23:35
Have you tried to set xml data limit to unlimited instead of 2 MB – Hadi Dec 12 '17 at 23:40
1

I did but no luck.. If its working for you, It is supposed to work for me.. Something isn't right on my SSMS. :( – BetterLateThanNever Dec 13 '17 at 00:00

score -1 · Answer 2 · answered Dec 12 '17 at 22:32

-1

I think the answer is the actual table in the top of your question, You cant relate values of a key in that way in database tables

answered Dec 12 '17 at 22:32

developer_009

317
3
11

This is not correct as you can select out data and display it differently to how it is stored. Also the table shown above does not have a primary key defined and key is not unique. – David B Dec 12 '17 at 22:38
Why can't you create a table with both columns as primary keys, so when you need records with the key 2149 for example you get all the values that associate with that key ? – developer_009 Dec 12 '17 at 22:41
1

Like the following? Key 2149 Value 805501,805502. This should be done in the presentation of data not in the way its stored. Storing like this would prove difficult in the future to retrieve values. – David B Dec 12 '17 at 22:49

T SQL - Projecting multiple records into one row for each key

2 Answers2