sql crosstab problem

Question

Here's the very detail prob: this date belongs only to 1 table

custcode  address
cust1     capitol, cebu city
cust1     gen. maxilom, cebu city
cust1     guadalupe, cebu city
cust2     paknaan, mandaue city
cust2     basak, mandaue city
cust3     lapu-lapu city

In my report I want to have this fields in my reportviewer

customer name  location1           location2                location3
cust1          capitol, cebu city  gen. maxilom, cebu city  guadalupe, cebu city
cust2          paknaan, mandaue    basak, mandaue           lapu-lapu city

please help..

Answer 1

If you have a location field that you haven't shown us that can distinguish what is meant to go in each column

SELECT 
custcode, 
MAX(CASE WHEN location = 1 THEN address END) AS location1,
MAX(CASE WHEN location = 2 THEN address END) AS location2,
MAX(CASE WHEN location = 3 THEN address END) AS location3
FROM X
GROUP BY custcode

If you are relying on row ordering A SQL Server specific answer.

This assumes that you have an ID field from which the order of the "first" row can be calculated.

with X as
(
SELECT 1 AS ID, 'cust1' AS  custcode, 'capitol, cebu city' AS address 
UNION ALL
SELECT 2 AS ID,  'cust1' AS  custcode, 'gen. maxilom, cebu city' AS address 
UNION ALL
SELECT 3 AS ID,  'cust1' AS  custcode, 'guadalupe, cebu city' AS address 
UNION ALL
SELECT 4 AS ID,  'cust2' AS  custcode, 'paknaan, mandaue city' AS address 
UNION ALL
SELECT 5 AS ID,  'cust2' AS  custcode, 'basak, mandaue city' AS address 
UNION ALL
SELECT 6 AS ID,  'cust2' AS  custcode, 'lapu-lapu city'
)
, Y AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY custcode ORDER BY ID) AS RN, 
       custcode,
       address 
FROM X
)

SELECT custcode, [1] AS location1 , [2] AS location2,[3] AS location3 FROM Y
PIVOT  
(  
Max(address)  
FOR RN IN ([1], [2],[3])  
) AS PivotTable;