CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Newbie
Newbie

Reputation: 49

Retrieving unique names and based on that fetching the dates

I have two stored procedures like below:

Select distinct TempName from History_Table 

Select TempName,RunDate from History_Table  where TempName=@tempname

How do I use a single stored procedure to get a result set of distinct tempname's and their associated RunDate?

  TempName     RunDate                       RunBy

1   test    2012-10-11 00:00:00.000 chandu
2   testing 2012-12-15 00:00:00.000 kumar
3   asdsad  2012-08-09 00:00:00.000 asdsad
4   test    2012-12-12 00:00:00.000 asdasds
15  test    2012-10-01 00:00:00.000 asdaddf
15  test    2012-09-12 00:00:00.000 bghgh

I have to display like

TempName   RunDate                   RunBy
test       dates(in a dropdown)      lastrun name(i.e asdasds)

Upvotes: 1

Views: 72

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Result sets from SQL have to follow a simple, tabular shape. There's no simple way to get a result where there's one row, of which one column contains multiple values (there are some ugly hacks, I won't go into).

The cleanest way to do this, so far as I'm concerned, is to run the following query:

Select TempName,RunDate,RunBy from History_Table ORDER BY TempName,RunDate

Then, in whatever is consuming the results from this table, it should keep track of the last TempName value it processed. If the current row has the same TempName value, then it should add RunDate to the existing drop-down. Otherwise, it should create a new row for the new TempName and create a drop-down with exactly one RunDate value.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166576

From the comments you could try something like (SQL Server 2005 + CTE) 

;WITH UNames AS (
        Select  distinct 
                TempName 
        from    History_Table 
)
Select  ht.TempName,
        ht.RunDate 
from    History_Table  ht INNER JOIN
        UNames u    ON  ht.TempName = u.TempName

Upvotes: 1

Related Questions