transposing rows to columns

[MaffewW]

Hi all

I need to denormalise a table, the export I need to create is a flat file containing the information from the customer table, then each entry from the creditor table appended to the end.

The number of export columns need to increase to however many creditors there are for the customer with the most creditors, so in the example below there will be the crd and pay columns will be replicated 3 times. In theory there is no maximum number of entries on the creditors table though realistically will be up to about 10. I've added a counter to the table called custinstance for each customer to help

It needs to be done in SQL. I've got some ideas how to do it though its a bit messy, anyone else tried something similar I'd be interested to see your approach/ideas

All the best


Customer table
custID Surname
1 Test1
2 Test2
3 Test3

Creditor table
custID Crd Pay custinstance
1 B1 100 1
1 B2 150 2
2 B4 200 1
2 B4 275 2
2 B1 100 3
3 B6 50 1

Export
custID Surname Crd1 Pay1 Crd2 Pay2 Crd3 Pay3
1 Test1 B1 100 B2 150
2 Test2 B4 200 B4 275 B1 100
3 Test3 B6 50

Matt

Brighton, UK

 

[mrdenny]

We were just talking about something like this in this thread thread183-1068258. Look at the second batch of code I posted.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)