asked on

SQL - PIVOT

Hi there, Can someone help with this please? I think the PIVOT function will help?

File attached

TIA, Roger

Source
 
postcode		cust
AB10		AMI004
AB10		CLI001
AB10		HCL001
AB10		MIL003
AB10		OSB001
AB11		AMI004
AB11		CLI001
AB11		HCL001
AB11		MIL003
AB11		OSB001
AB12		AMI004
AB12		CLI001
AB12		ABC001
AB12		THK010
AB12		NHF001
AB12		HGF002
 
 
Output required
 
 
postcode		cust
 
AB10		AMI004, CLI001, HCL001, MIL003, OSB001
AB11		AMI004, CLI001, HCL001, MIL003, OSB001
AB12		AMI004, CLI001, ABC001, THK010, NHF001, HGF002

Open in new window

Pivot-Test-Data.txt

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rogero

ASKER

Thankyou, that works brilliantly .. I will look into 'for xml path' to understand what you did. Thanks again :-)

Kevin Cross

You are most welcome.

I meant to explain further and just got into a rush. Here is a reference to the Microsoft books online.
http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

The For Xml statement is a nice feature in SQL Server 2005+ to convert your query to Xml. The Path keyword used after would identify the element, then there is a Root to identify the singular top level Xml.

e.g., to yield this result

<PostalCodeCusts>
<PostalCodeCust>
<PostalCode>AB10</PostalCode>
<Cust>AMI004</Cust>
</PostalCodeCust>
</PostalCodeCusts>

You would construct a query like:

select PostalCode, Cust
from your_table_name
for xml path('PostalCodeCust'), root('PostalCodeCusts')

Hope that helps get you started.

Regards,
Kevin