I want to convert rows into columns.. So i am using PIVOT. I am getting half result..
I am not getting how to use more than one row into pivot. Pls help me.. Its very much needed.
Below is my dynamic pivot code.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Courses.ecg_def_name )
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY ec.ecg_cat_name DESC) AS row,
df.ecg_def_id,
df.ecg_def_name
FROM tbl_ecg_def_defect df
LEFT JOIN tbl_ecg_cat_category ec
ON df.ecg_def_cat=ec.ecg_cat_id) AS Courses
print @ColumnName
SET @DynamicPivotQuery =
N'SELECT ' + @ColumnName + ' FROM(
SELECT cnt.ecg_ent_count,
def.ecg_def_name
from tbl_ecg_cent_entery cnt
LEFT JOIN tbl_ecg_def_defect def
on def.ecg_def_id = cnt.ecg_def_id
LEFT JOIN tbl_ecg_ent_entery ent
on ent.ecg_ent_id = cnt.ecg_ent_id
WHERE ent.ecg_ent_date =''2015-08-04'' and ent.ecg_ent_insertedby = 4956 and ent.ecg_ent_prj = 4
) ent
PIVOT(SUM(ecg_ent_count)
FOR ecg_def_name IN (' + @ColumnName + ')) AS PVTTable;'
--Execute the Dynamic Pivot Query
print @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery
OUTPUT:
Documentation System Function
1 2 1
In this code i am getting def name in rows. I want to add date and quantity.
Below is static code
SELECT ecg_ent_date [Date],ecg_ent_quantity [Qty],[Documentation],[System],[Function]
FROM (
SELECT cnt.ecg_ent_count,
def.ecg_def_name ,
ecg_ent_date,
ecg_ent_quantity
from tbl_ecg_cent_entery cnt
LEFT JOIN tbl_ecg_def_defect def
on def.ecg_def_id = cnt.ecg_def_id
LEFT JOIN tbl_ecg_ent_entery ent
on ent.ecg_ent_id = cnt.ecg_ent_id
WHERE ent.ecg_ent_date ='2015-08-05' and ent.ecg_ent_prj = 1
) ent
PIVOT (
SUM(ecg_ent_count) FOR ecg_def_name IN([Documentation],[System],[Function])
) Result;
OUTPUT:
Date Qty Documentation System Function
2015-08-05 2 1 2 1
How to add Date and Qty dynamically using pivot