Need Help Rearranging SQL resultset!

Morpheus

Firstly, im not a college newby, im not doing some project that im cheating on, I work for a company based in Dublin, however my knowledge of SQL sometimes isnt the best and I was wondering is there anyway to do this easily???


I currently have a select statement that produces a result like this

SELECT 

A.ApplicationID,
C.CustomerName,
CA.Town,
FI.ItemName,
CF.ValueRequested

FROM t_Application AS A

INNER JOIN t_ApplicationApplicant AS AA ON A.ApplicationID = AA.ApplicationID
INNER JOIN t_Customer AS C ON AA.CustomerID = C.CustomerID
INNER JOIN t_CustomerAddresses AS CA ON C.CustomerID = CA.CustomerID
INNER JOIN t_CustomerFacilities AS CF ON C.CustomerID = CF.CustomerID
INNER JOIN t_Facilities AS F ON CF.FacilityID = F.FacilityID
INNER JOIN t_FacilityItems AS FI ON F.FacilityID = FI.FacilityID 
INNER JOIN t_DataTypes AS DT ON F.DataTypeID = DT.DataTypeID

WHERE A.ApplicationID = 3 
AND ( FI.ItemName = '2in1' OR FI.ItemName = '3in1')

This produces the following result set.....
1 John Swords 2in1 0
2 Paul Skerries 2in1 1
1 John Swords 3in1 2
2 Paul Skerries 3in1 1

How would I get the resuts to appear like this, as 2 rows instead of 4??
1 John Swords 2in1 0 3in1 2
2 Paul Skerries 2in1 1 3in1 1

I tried using a DISTINCT but that failed, I may need a union??

Kai

Im assuming your using MS SQL Server, you could try a CASE statement. Its going to get trickey though if you have a large number of ItemNames.

SELECT 

A.ApplicationID,
C.CustomerName,
CA.Town,
SUM(
CASE FI.ItemName WHEN '2in1' THEN CF.ValueRequested ELSE 0 END 
) as '2in1',
SUM(
CASE FI.ItemName WHEN '3in1' THEN CF.ValueRequested ELSE 0 END 
) as '3in1'

FROM t_Application AS A

INNER JOIN t_ApplicationApplicant AS AA ON A.ApplicationID = AA.ApplicationID
INNER JOIN t_Customer AS C ON AA.CustomerID = C.CustomerID
INNER JOIN t_CustomerAddresses AS CA ON C.CustomerID = CA.CustomerID
INNER JOIN t_CustomerFacilities AS CF ON C.CustomerID = CF.CustomerID
INNER JOIN t_Facilities AS F ON CF.FacilityID = F.FacilityID
INNER JOIN t_FacilityItems AS FI ON F.FacilityID = FI.FacilityID 
INNER JOIN t_DataTypes AS DT ON F.DataTypeID = DT.DataTypeID

WHERE A.ApplicationID = 3 
AND ( FI.ItemName = '2in1' OR FI.ItemName = '3in1')

GROUP BY A.ApplicationID,
C.CustomerName,
CA.Town

Not sure if that validates or even if it will work straight off but you get the idea. It wont give you what your looking for exactly but it will put the 2in1 and 3in1 into seperate columns.
Read up about CASE statements as they are really useful.

Morpheus

Case statements eh?

Never thought of them... thanks!