Error with the pivot table in sql server 2005

Posted by Rajamani under Sql Server on 9/25/2012 | Points: 10 | Views : 1020 | Status : [Member] | Replies : 2
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





Responses

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

Up
0
Down
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

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

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

Up
0
Down
hi

Please mark as answer if it helpful to you.

Mark as Answer if its helpful to you

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

Login to post response