Error with the pivot table in sql server 2005

Posted by Rajamani under Sql Server on 9/25/2012 | Points: 10 | Views : 1975 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hello sir, my query are executed. but i had a problem my result it would show the Same customer_Name in many time. so how to group by the Customer_Name in my query

alter procedure sp_Example
as
begin

DECLARE @colsPivot AS NVARCHAR(MAX),
    @colsUnpivot as NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(Product_Name) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
      from
      (
        select Customer_Name,Product_Name,Quantity, Revenue
        from test
)X1
              pivot
      (
        max(Quantity)
        for Product_Name in ('+ @colspivot +')
      ) p'
exec(@query)
end

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Jayakumars on: 9/29/2012 [Member] [MVP] Bronze | Points: 25

0	hi this is sample for pivot USE AdventureWorks GO SELECT [CA], [AZ], [TX] FROM ( SELECT sp.StateProvinceCode FROM Person.Address a INNER JOIN Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID ) p PIVOT ( COUNT (StateProvinceCode) FOR StateProvinceCode IN ([CA], [AZ], [TX]) ) AS pvt; Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Rajamani, if this helps please login to Mark As Answer. \| Alert Moderator

hi

this is sample for pivot


USE AdventureWorks

GO

SELECT [CA], [AZ], [TX]

FROM

(

SELECT sp.StateProvinceCode

FROM Person.Address a

INNER JOIN Person.StateProvince sp

ON a.StateProvinceID = sp.StateProvinceID

) p

PIVOT

(

COUNT (StateProvinceCode)

FOR StateProvinceCode

IN ([CA], [AZ], [TX])

) AS pvt;

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Rajamani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 9/29/2012 [Member] [MVP] Bronze | Points: 25

0	hi Please mark as answer if it helpful to you. Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Rajamani, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts