I am using MS Sql2008,
Table1: Plan.Plan
Table2: Plan.PlanFeature
Table3:Plan.PlanDetails
Plan.Plan
PlanID_PK PlanName AnnualPrice MonthlyPrice
1 Plan1 Free Free
2 Plan2 $50.00 $4.99
3 Plan3 $100.00 $9.99
Plan.PlanFeature
PlanFeatureID_PK FeatureName
- - - - - - - - - - - - - - - - - - - - - - - - - - -
1 Feature1
2 Feature2
3 Feature3
4 Feature4
Plan.PlanDetails
PlanDetailsID_PK PlanID_FK PlanFeatureID_FK Quantity Quantity_Type
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - -
1 1 1 0 Included
2 1 2 0 Unlimited
3 1 3 2 None
4 1 4 0 Unlimited
5 2 1 0 Included
6 2 2 0 Unlimited
7 2 3 20 None
8 2 4 0 Unlimited
9 3 1 0 Included
10 3 2 0 Unlimited
11 3 3 >20 None
12 3 4 0 Unlimited
Output :
FeatureName Plan1 Plan2 Plan3
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Feature1 Included Included Included
Feature2 Unlimited Unlimited Unlimited
Feature3 2 20 >20
Feature4 Unlimited Unlimited Unlimited
AnnualPrice Free $50.00 $100.00
MonthlyPrice Free $4.99 $9.99
So here we go, For converting rows into columns I used COALESCE and Pivot
Here the stored procedure I am trying it,
DECLARE @cols NVARCHAR(2000)
DECLARE @SubjectQuery NVARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + PlanNames + ']',
'[' + PlanNames + ']')
FROM Plans.Plans
ORDER BY PlanNames
SET @SubjectQuery = 'SELECT FeatureName, ' + @cols + '
FROM
(select p.PlanId_PK, p.PlanNames,pf.FeatureName,pd.Quantity from Plans.PlanDetails pd
join Plans.Plans p on pd.PlanId_FK=p.PlanId_PK
join Plans.PlanFeatures pf on pd.PlanFeatureId_FK=pf.PlanFeatureId_PK ) S
PIVOT
(
Count(Quantity) --Dont know which aggregate functions i have to use here according to my output
FOR PlanNames IN
(' + @cols + ')) AS pvt'
exec @SubjectQuery
For quantity I have to check condition like this MAX(CASE WHEN pd.Quantity = '0' THEN pd.Quantity_Type)as Quantity, if the quantity value is 0 then i have to display Quantity_type values
Inside the pivot block don't know which aggregate function i have to use according to my output, can you please figure this out?
Based on your sample data and the desire output, I think you will want to use the case inside of the subquery, before the pivot:
DECLARE @cols NVARCHAR(2000)
DECLARE @SubjectQuery NVARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + PlanName + ']',
'[' + PlanName + ']')
FROM [Plans]
ORDER BY PlanName
SET @SubjectQuery
= 'SELECT FeatureName, ' + @cols + '
FROM
(
select p.PlanName,
pf.FeatureName,
MAX(CASE WHEN pd.Quantity = ''0'' THEN pd.Quantity_Type else quantity end)as Quantity
from PlanDetails pd
join [Plans] p
on pd.PlanId_FK=p.PlanId_PK
join PlanFeature pf
on pd.PlanFeatureId_FK=pf.PlanFeatureId_PK
group by p.PlanName, pf.FeatureName
) S
PIVOT
(
max(Quantity)
FOR PlanName IN (' + @cols + ')
) AS pvt'
exec(@SubjectQuery);
See SQL Fiddle with Demo.
The result is:
| FEATURENAME | PLAN1 | PLAN2 | PLAN3 |
---------------------------------------------------
| Feature1 | Included | Included | Included |
| Feature2 | Unlimited | Unlimited | Unlimited |
| Feature3 | 2 | 20 | >20 |
| Feature4 | Unlimited | Unlimited | Unlimited |
Edit, If you need the annual price and monthly price, then you will need to incorporate an unpivot and the pivot function:
DECLARE @cols NVARCHAR(2000)
DECLARE @SubjectQuery NVARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + PlanName + ']',
'[' + PlanName + ']')
FROM [Plans]
ORDER BY PlanName
SET @SubjectQuery
= 'SELECT FeatureName,'+@cols+'
FROM
(
select PlanName, FeatureName,
MAX(CASE WHEN Quantity = ''0'' THEN Quantity_Type else quantity end)as Quantity,
SortOrder
from
(
select p.PlanName, pf.FeatureName, pd.Quantity, pd.Quantity_Type, 1 Sortorder
from PlanDetails pd
join [Plans] p
on pd.PlanId_FK=p.PlanId_PK
join PlanFeature pf
on pd.PlanFeatureId_FK=pf.PlanFeatureId_PK
union all
select PlanName, col, ''0'', value, 2 SortOrder
from
(
select PlanID_PK, PlanName,
AnnualPrice,
cast(MonthlyPrice as varchar(6)) MonthlyPrice
from plans
) src
unpivot
(
value
for col in (annualprice, monthlyprice)
) unpiv
) pl
group by PlanName, FeatureName, SortOrder
) d
PIVOT
(
max(Quantity)
FOR PlanName IN ('+@cols+')
) AS pvt
order by SortOrder'
exec(@SubjectQuery);
Result:
| FEATURENAME | PLAN1 | PLAN2 | PLAN3 |
----------------------------------------------------
| Feature1 | Included | Included | Included |
| Feature2 | Unlimited | Unlimited | Unlimited |
| Feature3 | 2 | 20 | >20 |
| Feature4 | Unlimited | Unlimited | Unlimited |
| AnnualPrice | Free | 50.00 | 100.00 |
| MonthlyPrice | Free | 4.99 | 9.99 |