Not sure where to begin on query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Not sure where to begin on query

Author

Topic

morleyz
Starting Member

17 Posts

Posted - 2010-11-01 : 13:03:46

We have a database that we use for processing payments off a website. We can customize forms for payments with different fields, etc. based on the need. We would like to have a query that can pull both the payment info and the custom field info, but I don't know where to start...

The important tables and relevant fields are setup the following way:

table payment_forms (forms that can be assigned to a payment on our website):
form_id (identity)
Name (name of the form)

table form_fields (these are the individual fields, like First Name, Last Name, Address, etc.):
field_id (identity)
field_name (text description)
field_type (drop down, text box, etc.)

table fields_to_form (links the fields to the forms):
field_id
form_id
order (the order it appears on this form)

table payments (records payment information from paypal):
payment_id (identity)
form_id
transaction_id (from paypal)
amount (dollar amount)

table field_values (records user submitted information)
payment_id
field_id
field_value

What we would like to do is create a query that would export rows with the full payment information such as:
Payment ID, Transaction ID, Amount, Field 1 Value, Field 2 Value, etc.

I started with:
select * FROM payments
INNER JOIN field_values ON field_values.payment_id = payments.id

Which returns the data I want, but puts each field on 1 line, so it would like this:
Payment 1 ID, Transaction 1 ID, Amount 1, Field 1 Value
Payment 1 ID, Transaction 1 ID, Amount 1, Field 2 Value
Payment 2 ID, Transaction 2 ID, Amount 2, Field 1 Value
Payment 2 ID, Transaction 2 ID, Amount 2, Field 2 Value

What can I do to combine the fields into a single row (if even possible). I would prefer to have the SQL server do the work rather than having the front-end application loop through the data and pull the values.

Any help would be greatly appreciated.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 13:19:59

something like


select payment_id,transaction_id,
SUM(CASE WHEN field_id = 'Field 1 Value' THEN Amount1 ELSE 0 END),
SUM(CASE WHEN field_id = 'Field 2 Value' THEN Amount2 ELSE 0 END),
...
 FROM payments
INNER JOIN field_values ON field_values.payment_id = payments.id
GROUP BY payment_id,transaction_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources