Moving Several fields into 1 Row

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

SQL Server 2008 Forums

Transact-SQL (2008)

Moving Several fields into 1 Row

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2012-04-03 : 13:34:04

I have the following situation.

I have some data as follows

Name Item

Joe 123
Joe 456
Joe 789

If I want to take everything that's under the Name Colum and basically concat the Item Numbers into 1 field.

Such as

Name Item

Joe 123, 456, 789

What is the best way of going about that ? Pivot I dont think would work and I've used over by partion before and those both break things out into seperate colums.

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-03 : 14:40:20

always provide sample data in order for you to get quick response


declare @table table(names varchar(255))
insert into @table 
values('Joe 123')

insert into @table 
values('Joe 456')

insert into @table 
values('Joe 789')

SELECT
  STUFF(
    (
    SELECT
      ', ' + names
    FROM @table 
    FOR XML PATH('')
    ), 1, 1, ''
  ) As concatenated_string

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ZMike
Posting Yak Master

110 Posts

Posted - 2012-04-04 : 14:24:57

yosiasz,

This is doing wht I want for the most part.

I wanted it to list name in a column by it self and then all the items that that name has in it's own column

Name Items

Joe 123, 456, 789

I know the code you provided does what I need but how to I only put fields that I want into that single column ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:37:36

[code]

SELECT Name,
STUFF((SELECT ',' + Item FROM Table WHERE Name = t.Name ORDER BY Item FOR XML PATH('')),1,1,'') AS Items
FROM (SELECT DISTINCT Name FROM Table)t
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources