multiple select in one sp

I was wondering if it's possible to have a stored procedure that hastwo(or more) select statements which you can combine as a single result set.I am not able to use union as my select returns different number of columns.My selects are like this,This example is using only two table but i can have more then 2 tables in situation..

ELECT SUM(col1) AS sumcol1
FROM tbl1
WHERE (ID = @.para1')

SELECT SUM(col1) AS sumcol1, SUM(col2) AS sumcol2, SUM(col3)
AS sumcol3, SUM(col4) AS sumcol1
FROM tbl2
WHERE (id = @.para1).

thanks

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

|||

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

Both can be accessed from C#/VB, but they are separate result sets.

If a single result set is needed, then pad out the two queries with innocuous (sp?) dummy columns and union all them together.

|||

david wendelken:

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

That's right. That's because I thought that was what the questioner wanted. Now I've re-read the original question, I can see it isn't.

Doh.

|||

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

|||

nb123:

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

cast(null as int) as dummy_int
cast(null as varchar(6)) as dummy_varchar6

or just use 0 or '' if zero or an empty space works better for you

|||

Thanks David,it works

Multiple Rows to One Column Results

I want to display the top 5 count of areacodes for each name
I

want to combine all the results for the areacodes into one column for

each user. like a csv. I tried my code below but the results just

return the same 5 of areacodes for all names on each area code row with

each callername. like
joe blow 123,456,755,312,465,567,555
bill jones 123,456,755,312,465,567,555

I just want the top 5 for each particular name.
I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing!
Thanks

DECLARE @.Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacodes int)
declare @.MyAreaCodes varchar(50)
INSERT @.Stage
(
CallerName,
AreaCode,
Calls,
theDate
--myAreacodes

) --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name
SELECT CallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC

-- Get Calls
SELECT s.CallerName,
s.AreaCode,
s.Calls,
s.theDate--,myareacodes
FROM @.Stage s
INNER JOIN (
SELECT CallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @.Stage
GROUP BY CallerName

HAVING (CallerName = 'name1') OR
(CallerName = 'name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BY callername,Calls desc
--
set @.MyAreaCodes =''
--
SELECT top 5 @.MyAreaCodes = @.MyAreaCodes + ISNULL(AreaCode,'') + ',' from @.Stage

--
SELECT CallerNAme,@.MyAreaCodes AS MyAreaCodes from @.stage
Group By CallerName

This should do it

DECLARE @.Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacodes int)
declare @.MyAreaCodes varchar(50)
INSERT @.Stage
(
CallerName,
AreaCode,
Calls,
theDate
--myAreacodes
) --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name
SELECT CallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-17'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC

select CallerName, coalesce(AreaCode1 + ', ' + AreaCode2 + ', ' + AreaCode3 + ', ' + AreaCode4 + ', ' + AreaCode5, AreaCode1 + ', ' + AreaCode2 + ', ' + AreaCode3 + ', ' + AreaCode4, AreaCode1 + ', ' + AreaCode2 + ', ' + AreaCode3, AreaCode1 + ', ' + AreaCode2, AreaCode1)
from (select s0.CallerName, s1.AreaCode AreaCode1, s2.AreaCode AreaCode2, s3.AreaCode AreaCode3, s4.AreaCode AreaCode4, s5.AreaCode AreaCode5
from (select CallerName, min(RowID) as RowID from @.stage group by CallerName) s0
left outer join @.stage s1 on s1.CallerName = s0.CallerName AND s1.RowID = s0.RowID
left outer join @.stage s2 on s2.CallerName = s0.CallerName AND s2.RowID = s0.RowID + 1
left outer join @.stage s3 on s3.CallerName = s0.CallerName AND s3.RowID = s0.RowID + 2
left outer join @.stage s4 on s4.CallerName = s0.CallerName AND s4.RowID = s0.RowID + 3
left outer join @.stage s5 on s5.CallerName = s0.CallerName AND s5.RowID = s0.RowID + 4) Top5Calls

Multiple rows into single column

I'm working on a Reports and I need to combine data from multiple
rows (with the same EventDate) into one comma separated string. This is how the data is at the moment:

EmployeeID EventDate
2309 2005-10-01 00:00:00.000
2309 2005-10-01 03:44:50.000
2309 2005-10-01 08:59:00.000
2309 2005-10-01 09:29:44.000

I need it in the following format:

EmpID EventDate EventTime
2309 2005-10-01 00:00:00,03:44:50,08:59:00,09:29:44

There is no definite number of EventTime per Employee for a particular EventDate. And i don't want to use Iterative Methods.

Thanks & Regards
Rajan

Hi Rajan,

If you have access to sql server magazine take a look at this article, http://www.sqlmag.com/Article/ArticleID/93907/sql_server_93907.html

You can see the code even if you don't have membership so should still be useful. Listing 3 will probably be the most useful, although it is not particularly efficient.

Hope this helps.

Chris

|||

Another alternative might be something like:

declare @.mockup table
( EmployeeId integer,
EventDate datetime
)
insert into @.mockup values (2308, '1/1/2005')
insert into @.mockup values (2309, '10/1/2005')
insert into @.mockup values (2309, '10/1/2005 3:44:50')
insert into @.mockup values (2309, '10/1/2005 8:59')
insert into @.mockup values (2309, '10/1/2005 9:29:44')
insert into @.mockup values (2310, '11/1/2005')

select employeeId,
replace(replace(
( select e.eventDate as [data()]
from @.mockup e
where a.employeeId = e.employeeId
order by e.eventDate
for xml path ('')
), ' ', ','), 'T', ' ')
as EventDates
from @.mockup a
group by employeeId
order by employeeId

-- employeeId EventDates
-- -- -
-- 2308 2005-01-01 00:00:00
-- 2309 2005-10-01 00:00:00,2005-10-01 03:44:50,2005-10-01 08:59:00,2005-10-01 09:29:44
-- 2310 2005-11-01 00:00:00