February 25, 2011 at 12:51 am
Hi All
I would like to know how can I get SQL Server 2005 or 2008 to create a blank row before each new unique order number in a record. for e.g
I have a table that has an Order number but multiple Actions. The blank row would solve a problem i have that with a claculation that is happpening against each order but for simplicity sake the query below
Select OrdNo, Actions
from Orders
Returns:
OrdNo Actions
ORD011 Placed order
ORD011 collected from supplier
ORD011 Dispatched
ORD034 ORder query
ORD034 Sent Reply via email
ORD049 Placed order
ORD049 collected from supplier
ORD049 Returned to Supplier wrong colour
I would like it to return:
ORD011 Placed order
ORD011 collected from supplier
ORD011 Dispatched
ORD034 ORder query
ORD034 Sent Reply via email
ORD049 Placed order
ORD049 collected from supplier
ORD049 Returned to Supplier wrong colour
February 25, 2011 at 1:16 am
Hi,
if the amount of data is not too big you can take a cursor to iterate through every line of the select statement odered by OrdNo, Actions and check in every row whether the last row has the same OrdNo as the current row. If so then insert the line into a temporary table and if not insert a blank row before inserting the order line.
I think, that's it.
Greets
pitcher
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
February 25, 2011 at 9:37 am
This sounds like it is a job for the front end and not sql. sql is just an engine to store and retrieve data. Formatting is best left to whatever consumes the data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply