Combining data of a field with multiple values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining data of a field with multiple values

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2012-07-10 : 09:19:27

I would like to create a view that combines some data into one string. So for each person's record in the database (SQL Server 2005), using the "attribute" field which can contain zero to many values per record, I would like those values placed into one string. For example, if id 0000001 has three attribute values of:

Math
English
Biology

Then I want the field in the view to be Math, English, Biology

If these value were stored in different tables, it would be simple to combine. Since there are multiple instances of the same field, I am not sure how to approach this???

Thanks.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-10 : 09:48:57

Can you provide a more visual idea of the data you you are looking for, example I am assuming this is what you mean:


ID           Attribute
=========    =========
00000001     math
00000001     English
00000001     Biology

and this is what you want:


ID           Attribute
=========    =========
00000001     math, English, Biology

Bazinga
Starting Member

19 Posts

Posted - 2012-07-10 : 10:01:23

Exactly as you have wrote above, but I need a string for each ID. For example:

ID Attribute
========= =========
00000001 math
00000001 English
00000001 Biology
00000002 Biology
00000002 Physics
00000003 History
00000003 Biology

TO

ID Attribute
========= =========
00000001 math, English, Biology
00000002 Biology, Physics
00000003 History, Biology
Etc...
Etc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 10:20:47

[code]
SELECT ID,
STUFF((SELECT ',' + Attribute FROM YourTable WHERE ID = t.ID FOR XML PATH('')),1,1,'') AS AttributeList
FROM (SELECT DISTINCT ID FROM YourTable)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bazinga
Starting Member

19 Posts

Posted - 2012-07-10 : 10:47:07

quote:
Originally posted by visakh16

SELECT ID,
STUFF((SELECT ',' + Attribute FROM YourTable WHERE ID = t.ID FOR XML PATH('')),1,1,'') AS AttributeList
FROM (SELECT DISTINCT ID FROM YourTable)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This works. However there are space gaps in the new string AttribueList. For example,

0000000001 Do Not Mail.........................,Do Not Telephone
0000000002 Graduated with Honors,Golf

(Edit: placed the periods to show gap.)
What is causing those gaps?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-10 : 11:33:15

What is the data type of the column(s) that have trailing spaces? You could apply the RTRIM and/or LTRIM function(s)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 12:32:44

quote:
Originally posted by Bazinga

quote:
Originally posted by visakh16

SELECT ID,
STUFF((SELECT ',' + Attribute FROM YourTable WHERE ID = t.ID FOR XML PATH('')),1,1,'') AS AttributeList
FROM (SELECT DISTINCT ID FROM YourTable)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This works. However there are space gaps in the new string AttribueList. For example,

0000000001 Do Not Mail.........................,Do Not Telephone
0000000002 Graduated with Honors,Golf

(Edit: placed the periods to show gap.)
What is causing those gaps?

may be because Attribute column has spaces after actual data. first check that and try applying RTRIM

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources