thinklings
asked on
Select dynamic rows as columns?
Hi,
I have a CTE that selects two sets of data. One called Months that will return back the last year worth of months, for example:
2015-07
2015-08
2015-09
2015-10
2015-11
2015-12
2016-01
2016-02
2016-03
2016-04
2016-05
2016-06
2016-07
Another, contains the core data that returns data in the below column format:
ClientID MonthPeriod Spending
0001 2015-7 120.11
0001 2015-9 190.34
0001 2015-10 100.10
0001 2016-2 140.05
How do i query the above to so that it returns in the following format dynamically:
ClientID 2015-7 2015-8 2015-9 2015-10 2015-11 2015-12 2016-01 2016-02
0001 120.11 0.00 190.34 100.10 0.00 0.00 0.00 140.05
I have a CTE that selects two sets of data. One called Months that will return back the last year worth of months, for example:
2015-07
2015-08
2015-09
2015-10
2015-11
2015-12
2016-01
2016-02
2016-03
2016-04
2016-05
2016-06
2016-07
Another, contains the core data that returns data in the below column format:
ClientID MonthPeriod Spending
0001 2015-7 120.11
0001 2015-9 190.34
0001 2015-10 100.10
0001 2016-2 140.05
How do i query the above to so that it returns in the following format dynamically:
ClientID 2015-7 2015-8 2015-9 2015-10 2015-11 2015-12 2016-01 2016-02
0001 120.11 0.00 190.34 100.10 0.00 0.00 0.00 140.05
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window