Single row for multiple results
-
Hello, I would like to show data as a single row instead of multiple rows. For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row. Currently, John Smith Pending Approval John Smith Waiting Email Verfication Should be John Smith Pending Approval, Waiting Email Verfication There could such multiple states for this user, but all show in one row. Thanks so much for your time!
You can either build your state list directly in a stored procedure, or create a function that will build it for you. I encountered a similar situation yesterday: for a given item, multiple sources of data. I created a function to concatenate the items, comma delimited, and used the function in the query to return all of the data in a single row. Tim
-
You can either build your state list directly in a stored procedure, or create a function that will build it for you. I encountered a similar situation yesterday: for a given item, multiple sources of data. I created a function to concatenate the items, comma delimited, and used the function in the query to return all of the data in a single row. Tim
-
Hello, I would like to show data as a single row instead of multiple rows. For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row. Currently, John Smith Pending Approval John Smith Waiting Email Verfication Should be John Smith Pending Approval, Waiting Email Verfication There could such multiple states for this user, but all show in one row. Thanks so much for your time!
-
Hello, I would like to show data as a single row instead of multiple rows. For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row. Currently, John Smith Pending Approval John Smith Waiting Email Verfication Should be John Smith Pending Approval, Waiting Email Verfication There could such multiple states for this user, but all show in one row. Thanks so much for your time!
You can write your own aggregates with CLR Stored Procedures. CLR Stored Procedure and Creating It Step by Step[^] http://msdn.microsoft.com/en-us/library/ms190790.aspx[^] But I don't recommend it.
-
Hello, I would like to show data as a single row instead of multiple rows. For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row. Currently, John Smith Pending Approval John Smith Waiting Email Verfication Should be John Smith Pending Approval, Waiting Email Verfication There could such multiple states for this user, but all show in one row. Thanks so much for your time!
Assuming Sql Server 2005 and above, try this
Declare @t table([Name] varchar(20),[Status] varchar(50))
insert into @t values
('John Smith','Pending Approval'),('John Smith','Waiting Email Verfication')
--,('User2','Pending Approved'),('User2','Sent email'),('User2','Other activity')
--Select * from @tselect Result = [Name] + ' ' + Stuff((Select ',' + CAST([Status] as varchar(100)) from @t t2 where t1.[Name]
= t2.[Name] for XML Path('')),1,1,'')
from @t t1
group by t1.[Name]/*
ResultJohn Smith Pending Approval,Waiting Email Verfication
*/Niladri Biswas
-
Assuming Sql Server 2005 and above, try this
Declare @t table([Name] varchar(20),[Status] varchar(50))
insert into @t values
('John Smith','Pending Approval'),('John Smith','Waiting Email Verfication')
--,('User2','Pending Approved'),('User2','Sent email'),('User2','Other activity')
--Select * from @tselect Result = [Name] + ' ' + Stuff((Select ',' + CAST([Status] as varchar(100)) from @t t2 where t1.[Name]
= t2.[Name] for XML Path('')),1,1,'')
from @t t1
group by t1.[Name]/*
ResultJohn Smith Pending Approval,Waiting Email Verfication
*/Niladri Biswas
-
Thanks for your responses. Instead of a comma separated list, i would like it as line breaks. I am separating the information by pipes, and later doing a replace after calling the function like below. I am doing this -- SELECT replace(DBO.fn_getAssociateComments(168), '|', char(13)+char(10)) But it is putting any line breaks??Am I missing something? Thanks a bunch!
-
Thanks for your responses. Instead of a comma separated list, i would like it as line breaks. I am separating the information by pipes, and later doing a replace after calling the function like below. I am doing this -- SELECT replace(DBO.fn_getAssociateComments(168), '|', char(13)+char(10)) But it is putting any line breaks??Am I missing something? Thanks a bunch!
-
Assuming Sql Server 2005 and above, try this
Declare @t table([Name] varchar(20),[Status] varchar(50))
insert into @t values
('John Smith','Pending Approval'),('John Smith','Waiting Email Verfication')
--,('User2','Pending Approved'),('User2','Sent email'),('User2','Other activity')
--Select * from @tselect Result = [Name] + ' ' + Stuff((Select ',' + CAST([Status] as varchar(100)) from @t t2 where t1.[Name]
= t2.[Name] for XML Path('')),1,1,'')
from @t t1
group by t1.[Name]/*
ResultJohn Smith Pending Approval,Waiting Email Verfication
*/Niladri Biswas
I keep bumping into a need for this. I'm tossing this into my code library. Very, very useful. In fact it was useful today in a problem.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...
-
Hello, I would like to show data as a single row instead of multiple rows. For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row. Currently, John Smith Pending Approval John Smith Waiting Email Verfication Should be John Smith Pending Approval, Waiting Email Verfication There could such multiple states for this user, but all show in one row. Thanks so much for your time!
Without knowing the stucture of your tables it's kinda hard to say, but one approach could be to pivot the data into a temp table. Then you can maniulate the results as needed. http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.