SQL Pivot table

Question

Hi,

 

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