SQLTeam.com | Weblogs | Forums

Row to Colum


#1

Hi to all,

I have a view on sql 2008 and it shows ItemCode, ItemDescription, DaysOnStock, and SalesRep, I need to know all the Reps that Sold the itemcode so I need to convert the Rep on Column Header but I dont know how because Pivot request a function and it dont has a Column for that

Example I have this :
ItemCode | Description | Days | Rep |
CODE1 | ITEM 1 | 90 | 02 |
CODE1 | ITEM 1 | 90 | 12 |
CODE1 | ITEM 1 | 90 | 13 |
CODE2 | ITEM 2 | 85 | 02 |

AND I Need this:
ItemCode | Description | Days | 02 | 12 | 13 |
CODE1 | ITEM1 | 90 | X | X | X |
CODE2 | ITEM2 | 85 | X | | |

My actual code is this:

SELECT TOP (100) PERCENT ItemCode, Description, Days, Rep
FROM dbo.ViewVK_StockAging90_Who_Sale
ORDER BY Days DESC

Thanks to all


#2

CREATE table #test(ItemCode varchar(10),Description varchar(20),Days int,rep int)
INSERT INTO #test(ItemCode,Description,Days,rep)
VALUES
('CODE1','ITEM 1',90,02),
('CODE1','ITEM 1',90,12),
('CODE1','ITEM 1',90,13),
('CODE2','ITEM 2',85,02)

SELECT ItemCode,Description,Days,[02], [12], [13]
FROM
(SELECT ItemCode, Description, Days,rep
FROM #test) p
PIVOT
(
COUNT (rep)
FOR rep IN
( [02], [12], [13])) as pvt
ORDER BY ItemCode


#3

I Dont believe it, it works, look soo simple but I dont had any idea about pivot, thank You so much Shilpash