CODEWITHSUNDEEP
sqlsql-server
Salem
Salem

Reputation: 349

Return dynamic columns by joining results of a stored function

I have a stored function called Fnc_MyFunc(@myDate). it takes a date parameter and return a table like this: 

user_id | count_laps
--------+-----------
1       | 85
2       | 37
5       | 55
12      | 48

i want to execute this for many dates (date interval).
with my function i whant a result like this: (laps per day for all users)

user_id | [2015-10-01] | [2015-10-02] | [2015-10-03] | ....
--------+--------------+--------------+--------------+--------------
1       | 85           | 2            | 66           | ....
2       | 37           | 58           | 85           | ....
5       | 55           | 33           | 75           | ....
12      | 48           | 44           | 55           | ....

Upvotes: 0

Views: 41

Answers (1)

Bert Wagner
Bert Wagner

Reputation: 881

This query should do what you want:

SELECT 
    u.userId,
    d1.count_laps as [2015-10-01]
    d2.count_laps as [2015-10-02],
    d3.count_laps as [2015-10-03]
FROM 
    TableOrQueryWithDistinctUserIds u
    LEFT JOIN Fnc_MyFunc('2015-10-01') d1 ON u.userId = d1.userId
    LEFT JOIN Fnc_MyFunc('2015-10-02') d2 ON u.userId = d2.userId
    LEFT JOIN Fnc_MyFunc('2015-10-03') d3 ON u.userId = d2.userId

You can then make it dynamic if necessary.

Upvotes: 1

Related Questions