PIVOT concept in sql server

Question

I am having a Stored Procedure as shown below:

ALTER PROC [dbo].[USP_GetDraftQuoteByQuoteID]
(@QuoteID int)
AS 
BEGIN
SELECT cl.Quote_ID,cl.PECCode,lpu.Description,cl.Site_ID,cl.Quantity
FROM ConfigurationList cl
LEFT OUTER JOIN LPU lpu
ON cl.PECCode=lpu.PECCode
WHERE Quote_ID = @QuoteID

I am getting the output as shown below:

Quote   PECCode      Description  Site ID    Quantity
9   NTK539PDE5       OCLD            IND        8
9   NTK525FAE5       NULL            BVT        4
9   NTK531YAE5       NULL            CAX        3
9   NTNM34TB         NULL            IND        5
9   NTK569HA         NULL            COX        8
9   NTNM70ER         NULL            CBA        4
9   CNMT278BR        NULL            IND        4
9   NTRU0411         NULL            BVT        8

But I would like to get the output as below:

Quote   PECCode      Description  IND   BVT   CAX   COX   CBA
9   NTK539PDE5       OCLD           8
9   NTK525FAE5       NULL                4
9   NTK531YAE5       NULL                      3
9   NTNM34TB         NULL           5                  
9   NTK569HA         NULL                             8      
9   NTNM70ER         NULL                                   4
9   CNMT278BR        NULL           4      
9   NTRU0411         NULL               8

Here, the Quantity is based on the PECCode and the Sites. I think that here we have to use the PIVOT concept. But I won't be able to achieve the desired result. Anybody please help out.

Answer 1

Try this:

SELECT QuoteID, PECCode, Desc, 
[IND] as IND_Site, [BVT] as BVT_Site, [CAX] as CAX_Site, [COX] as COX_Site, [CBA] as CBA_Site
FROM
(

SELECT cl.Quote_ID AS QuoteID
,cl.PECCode as PECCode
,lpu.Description as Desc
,cl.Site_ID as SiteID
,cl.Quantity as Quant
FROM ConfigurationList cl
LEFT OUTER JOIN LPU lpu
ON cl.PECCode=lpu.PECCode
WHERE Quote_ID = @QuoteID) T

PIVOT

(
   SUM (Quant)
   FOR
      SiteID IN ([IND], [BVT], [CAX], [COX], [CBA])
) as Pivoted_Table
ORDER BY PECCode