SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Moving Several fields into 1 Row		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ZMike
Posting Yak Master

110 Posts
Posted - 04/03/2012 :  13:34:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1608 Posts
Posted - 04/03/2012 :  14:40:20  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

ZMike
Posting Yak Master

110 Posts
Posted - 04/04/2012 :  14:24:57  Show Profile  Reply with Quote
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 ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 04/04/2012 :  18:37:36  Show Profile  Reply with Quote 


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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000