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
3 Replies
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Upendra Pratap ShahiPosted Aug 7, 2015, 3:39 AM
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
Upendra Pratap ShahiPosted Aug 7, 2015, 3:38 AM
SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot
Sujeet SumanPosted Aug 7, 2015, 1:59 AM