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)
 how to put multi results as single value

Author  Topic 

new_developer
Starting Member

18 Posts

Posted - 2013-11-14 : 10:50:08
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-14 : 11:00:56
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

18 Posts

Posted - 2013-11-14 : 11:06:59
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-14 : 11:22:10
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

18 Posts

Posted - 2013-11-14 : 11:57:03
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-14 : 12:25:56
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

352 Posts

Posted - 2013-11-14 : 12:27:10
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

18 Posts

Posted - 2013-11-14 : 12:32:20
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-14 : 13:57:23
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
   

- Advertisement -