SQL QUERY VIEW – HELP REQUIRED !

150 pts.
Tags:
SQL
SQL Query
I have an sql :

[strong]select customer invoice_no invoice_amount due_date from Table[/strong]

This gives a list of all details in rows

99999  1234 12.50 21/11/09

99999  1235 13.55 22/11/09

99998  7899 12.20 21/12/09

99998  7900 12.75 22/12/09

99998  7901 11.11 23/12/09

 

I want a line per customer containing all details

99999  1234 12.50 21/11/09 1235 13.55 22/11/09

99998  7899 12.20 21/12/09 7900 12.75 22/12/09  7901 11.11 23/12/09

Can anyone help me please ?  

 



Software/Hardware used:
sql query

Answer Wiki

Thanks. We'll let you know when a new response is added.

Oh! Now I understand what You’re asking. In the future, only post a question once, then carry on in the discussion thread. It keeps the forums a lot cleaner.

Unfortunately I’m pretty sure you can’t do what you’re suggesting in SQL Server, because that would require each Customer to have a dynamic amount of fields, and combine entries from the same table to get those fields. I would suggest just Ordering or Grouping by the Customer ID, and using the results that way. Do you just want to create these entries to print, or are you using this data within another program? Within that program you might be able to manipulate this output to your desired format.

<pre>select customer invoice_no invoice_amount due_date from Table ORDER BY Customer DESC</pre>

Gerrib,

Take a look at this link, it should help with you with your issue. What you are trying to do is a dynamic pivot.

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Thanks,

Curtis

The best way to handle this is in the client side code. Have the client code loop through the recordset stringing the data into a string until you get to the next customer record.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gerrib
    I can actually do that already What I really want to do is export these details and use them for emerging in Word. It appears that in Word one can create a letter (directory) per customer which includes details contained in multiple records for this customer in the datasource. Sadly this cannot be then be emailed so I was trying to create a datasource which holds all the details in one record.
    150 pointsBadges:
    report
  • Washkuhnw
    Okay...you didn't mention any of that before, but I digress. So I'll try to understand again...in word you have a record that holds all the details for this customer, but you can't email it. So you want to go back into SQL and combine all the details for the customer, like I addressed earlier? Couldn't you just host the word file, or find some other way to deliver it? It seems like much less work for a solution that you already have complete. If I'm not understanding again, please correct me. There seems to be a lot of miscommunication happening right now.
    90 pointsBadges:
    report
  • carlosdl
    I agree. Gerrib, please try to clarify what exactly you need help with. On the other hand, Washkuhnw, you can certainly do what Gerrib asked in the original question with Sql Server, but a function would be necessary for that.
    69,365 pointsBadges:
    report
  • carlosdl
    Well, maybe not exaclty what Gerrib asked, but something similar, returning all details concatenated in a field.
    69,365 pointsBadges:
    report
  • Gerrib
    To clarify the situation : Ultimately I want to e-merge letters to customers containing all their invoice details in one email. My datasource is coming from an oracle database. I extract the details using SQL for selected customers and dates. The datasource contains one record per customer invoice which looks like my original example. But using this the result would be that a customer could get multiple emails on each run - which would kind of upset them :-( I investiagted word and there is the facility to write code in the document to handle multiple records per customer inout in the datasource but this creates a directory which can then be printed but not emailed !!!! So... I thought perhaps I shoudl try and get my datasource to contain the multiple details. My background is RPG and this could be done easily by creating an array but I cannot do the same in SQL so that is why I came to this forum. I just wanted help to complete the task either using SQL to create a datasource which could be used OR any other method using the input I have to achieve the output required ie emailed letter with all invoice details per customer I hope this makes sense Regards
    150 pointsBadges:
    report
  • Gerrib
    Thanks Curtis for the link - I have setup the exmple table & used the code given as I think this is what I am lookin for but get "Line 7: Incorrect syntax near 'PIVOT'." Excuse my lack of knowledge but its all new to me Thanks again Gerrib Details of systems : SQL Server 2005 Microsoft SQL Server Management Studio Express 9.00.2047.00 Microsoft .NET Framework 2.0.50727.1873 Operating System 5.1.2600
    150 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following