how to put multi results as single value

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

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

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

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.

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources