June 7, 2013 at 4:25 am
Hi ,
What would be the best way to dynamically self join a table to create the example contained with the excel spreadsheet?
The number of rules can vary depending on the order. Is there a way of creating a script/function that will look at all rules applied to a order and create 1 line per order with all rules applied?
all suggestions would be welcome
Thanks in advance
Dan
June 7, 2013 at 8:28 am
DBayliss (6/7/2013)
Hi ,What would be the best way to dynamically self join a table to create the example contained with the excel spreadsheet?
The number of rules can vary depending on the order. Is there a way of creating a script/function that will look at all rules applied to a order and create 1 line per order with all rules applied?
all suggestions would be welcome
Thanks in advance
Dan
Effectively what you have going on here is a cross tab. The approach you need to take will depend on if you know what the maximum number of columns is or not. If the max number of columns is static you can use a cross tab, if you don't know the max number of number of columns you will need to use a dynamic cross tab.
You should take a peek at the article in my signature about cross tabs, it will explain how to do this in great detail with excellent examples.
I would recommend a couple things. First you not use sql reserved words as object or column names (RULE). It makes working with these a lot more difficult. Secondly, you seem to have some duplicated data. You have a column for ClientRef but that same value is part of the ClientOrder. From the data you posted you have nothing that can be a suitable key either.
One of the challenges you have going on here is there is no way to know what the order is. How do you know what is rule #1 or rule #2?
One thing we like to see around here is ddl (create table scripts) and sample data (insert statements). I created these for you so you can see what I mean. The first link in my signature has more details about best practices when posting questions.
The query below will produce the results you requested based on the sample data provided. This assumes that static max number of columns is 5.
--begin setup
if OBJECT_ID('tempdb..#Client') is not null
drop table #Client
create table #Client
(
ClientRef char(7),
ClientOrder char(10),
MyRule varchar(25)
)
Insert #Client
select 'AAMX005', 'AAMX005TW1', '10% Load APPLIED' union all
select 'AAMX005', 'AAMX005TW1', '5% Discount APPLIED' union all
select 'AAMX005', 'AAMX005TW2', '10% Load APPLIED' union all
select 'AAMX005', 'AAMX005TW2', '5% Discount APPLIED' union all
select 'AAMX005', 'AAMX005TW3', '10% Load APPLIED' union all
select 'AAMX005', 'AAMX005TW3', '5% Discount APPLIED' union all
select 'AAMX005', 'AAMX005TW4', '10% Load APPLIED' union all
select 'AAMX005', 'AAMX005TW4', '5% Discount APPLIED' union all
select 'AAMX005', 'AAMX005TW4', '9% Discount APPLIED' union all
select 'AAMX005', 'AAMX005TW4', '15% Discount APPLIED' union all
select 'AAMX005', 'AAMX005TW4', '19% Discount APPLIED';
--end setup
--Now we can start the solution
with MyCte as
(
select *, ROW_NUMBER() over (PARTITION by ClientOrder order by (select null)) as RowNum from #Client
)
select ClientOrder,
max(case when rowNum = 1 then MyRule else null end) as Rule1,
max(case when rowNum = 2 then MyRule else null end) as Rule2,
max(case when rowNum = 3 then MyRule else null end) as Rule3,
max(case when rowNum = 4 then MyRule else null end) as Rule4,
max(case when rowNum = 5 then MyRule else null end) as Rule5
from MyCte
group by ClientOrder
_______________________________________________________________
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply