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)
 how to put multi results as single value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

new_developer
Starting Member

Australia
18 Posts

Posted - 11/14/2013 :  10:50:08  Show Profile  Reply with Quote
hi for all I have a query return multi results
I want to find a way to put these results in one field
actualy to benifet from it in my main query which must return
a single record
this is my try
set @ss=(select Serial_Number_Items from items union select Item_Name from items)
in fact I want to put these two fields with their result as single value
can any one help me
thanks

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2013 :  11:00:56  Show Profile  Reply with Quote
please post an example of the individual values and also your desired single value so we can see what you mean.

Be One with the Optimizer
TG
Go to Top of Page

new_developer
Starting Member

Australia
18 Posts

Posted - 11/14/2013 :  11:06:59  Show Profile  Reply with Quote
quote:
Originally posted by TG

please post an example of the individual values and also your desired single value so we can see what you mean.

Be One with the Optimizer
TG


my friend see what I mean is to merge the results in single field
to appear as one value I hope my order is clear now
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2013 :  11:22:10  Show Profile  Reply with Quote
try this:
select Serial_Number_Items + ' - ' + item_name from items

Be One with the Optimizer
TG
Go to Top of Page

new_developer
Starting Member

Australia
18 Posts

Posted - 11/14/2013 :  11:57:03  Show Profile  Reply with Quote
quote:
Originally posted by TG

try this:
select Serial_Number_Items + ' - ' + item_name from items

Be One with the Optimizer
TG


I have tried it but it give me this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2013 :  12:25:56  Show Profile  Reply with Quote
if you post your actual statement we can help.

Be One with the Optimizer
TG
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
338 Posts

Posted - 11/14/2013 :  12:27:10  Show Profile  Reply with Quote
Something like?
select @ss = @ss + Serial_Number_Items from items 
select @ss = @ss + Item_Name from items


djj
Go to Top of Page

new_developer
Starting Member

Australia
18 Posts

Posted - 11/14/2013 :  12:32:20  Show Profile  Reply with Quote
quote:
Originally posted by TG

if you post your actual statement we can help.

Be One with the Optimizer
TG


yes my friend I will post it
see my friend I have one table for maintance and the maintance
may contain one or more of items which are in another table called
a maintance what I need is to have a query return a single maintance
depends on ID of maintance besides the items related to this
maintance in one record
this is my query


declare @ss nvarchar(max)=null
set @ss=(select item_name from items)

SELECT     Master_Maintenance.Serial_Name_Maintenance, Master_Maintenance.Request_Name, Master_Maintenance.Serial_Number_Request, 
                      Master_Maintenance.Floor_Number, Master_Maintenance.Room_Number, Master_Maintenance.Area_Location_Number, Master_Maintenance.Request_Status, 
                      Master_Maintenance.Request_Time, Master_Maintenance.Request_Date, Master_Maintenance.Department_Name, Master_Maintenance.Request_Discreption, 
                      Master_Maintenance.Technical_Name, Master_Maintenance.Request_Sumbitting_Date, Master_Maintenance.Request_Sumbitting_Time, 
                      Master_Maintenance.Serial_Number_Items, Master_Maintenance.Notes, Master_Maintenance.Request_Finished_date, 
                      Master_Maintenance.Requestl_Finished_Time, Master_Maintenance.Request_EXT, Master_Maintenance.MaintenanceType, Master_Maintenance.priority, 
                      Master_Maintenance.shutdown1, Master_Maintenance.est, Master_Maintenance.hourstaken,@ss
FROM         Master_Maintenance 
WHERE     (Master_Maintenance.Serial_Name_Maintenance = @Serial_Name_Maintenance )

I hope my question is clear
and thanks
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2013 :  13:57:23  Show Profile  Reply with Quote
Looks like the problem is with this statement because there is more than one row in items:

declare @ss nvarchar(max)=null
set @ss=(select item_name from items)


are you trying to get a delimited list of items something like this?
'Item1,Item2,Item3,Item4'

If so then this should work:

declare @ss nvarchar(max)
select @ss = stuff(itemList,1,1,'')
from   (
       select ',' + item_name 
       from items
       for xml path('')
       ) as x (itemList)




Be One with the Optimizer
TG
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.08 seconds. Powered By: Snitz Forums 2000