Solved

How to manipulate data from a SELECT statement, STUFF function

Posted on 2010-11-23
8
266 Views
Last Modified: 2012-08-13
I have a select that returns:
EQUIPMENT
Motor
Gear Box
Brake
Wheel

I want to show it as:
Motor-Gear Box-Brake-Wheel

I got started with the stuff function but doesn't seem to work. Can someone please help?
Can you also explain to me the xml path and stuff function.
(select distinct
	stuff((select ', ' + EQPMNT_NAME from EQUIPMENT WHERE EQPMNT_KEY=er.EQPMNT_KEY for xml path('')), 1, 2, '') As EQUIPMENT
FROM EQUIPMENT_REQUEST er 
WHERE RQUST_KEY=70)

Open in new window

0
Comment
Question by:codemonkey2480
  • 5
  • 2
8 Comments
 
LVL 6

Accepted Solution

by:
subhashpunia earned 400 total points
Comment Utility
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Try this


select distinct (stuff((select ', ' + EQPMNT_NAME from EQUIPMENT WHERE EQPMNT_KEY=er.EQPMNT_KEY for xml path('')), 1, 2, '')) As EQUIPMENT
FROM EQUIPMENT_REQUEST er
WHERE RQUST_KEY=70
0
 

Author Comment

by:codemonkey2480
Comment Utility
Brichsoft,
Thanks for the reply. How is this different from my query?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi,

not much difference.
Just ( difference.

You opened before select.

I opened after distinct.

normally,main query never within (

( used for sub query or when you wanted to join...

your main select should not be within (

- Bhavesh
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:codemonkey2480
Comment Utility
I get the same results, list of equipment like:
EQUIPMENT
Motor
Gear Box
Brake
Wheel


0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 100 total points
Comment Utility
Try this.


Declare @Table1 Table
(
      EQUIPMENT_KEY INT,
      EQUIPMENT varchar(50) not null
)

Insert @Table1
Select 1,'Motor'
Union All
Select 2,'Gear Box'
Union All
Select 3,'Brake'
Union All
Select 4,'Wheel'




Select * from @Table1

Select Distinct      
      (Stuff((Select ', ' + EQUIPMENT From @Table1 T2 FOR XML PATH('')),1,2,'')) as EQUIPMENT
From @Table1 T1
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility

select distinct (stuff((select ', ' + EQPMNT_NAME from EQUIPMENT for xml path('')), 1, 2, '')) As EQUIPMENT
FROM EQUIPMENT_REQUEST er
WHERE RQUST_KEY=70
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi,

It wont come....

see,you wanted EQUIPEMENT in 1 Row
You passing

Where T2.EQUIPMENT_KEY = T1.EQUIPMENT_KEY

as EQUIPMENT_KEY is unique,your equipment wont come comma seprated
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now