November 12, 2009 at 6:50 pm
Hi there,
I am hoping someone here can help me with a problem I have ran into. Below is the data, bascially each record has upto 8 sets of item/qty/value. (I have shown just 3 for convenience).
No Item1|Qty1|Value1 | Item2 | Qty2 | Value2 | Item2 | Qty2 | Value2
1 ABC 3 10.00 DEF 4 20.00 GHI 6 30.00
2 IEO 10 43.00 WER 18 12.00
The requirement is to split each set Item/Qty/Value on to its own line. Essentially it should look like this :
1 ABC 3 10.00
1 DEF 4 20.00
1 GHI 6 30.00
2 IEO 10 43.00
2 WER 18 12.00
I intentionally skipped the 3rd (item/qty/value) set from 2nd record as it is possible it could be empty.
I would appreciate any feedback on how to resolve this issue. BTW, I am using SQL2005.
When I have this straigthened , it will serve as my dataset for a SSRS report.
Thanks in advance.
TP
November 12, 2009 at 11:36 pm
tony.pinto (11/12/2009)
Hi there,I am hoping someone here can help me with a problem I have ran into. Below is the data, bascially each record has upto 8 sets of item/qty/value. (I have shown just 3 for convenience).
No Item1|Qty1|Value1 | Item2 | Qty2 | Value2 | Item2 | Qty2 | Value2
1 ABC 3 10.00 DEF 4 20.00 GHI 6 30.00
2 IEO 10 43.00 WER 18 12.00
The requirement is to split each set Item/Qty/Value on to its own line. Essentially it should look like this :
1 ABC 3 10.00
1 DEF 4 20.00
1 GHI 6 30.00
2 IEO 10 43.00
2 WER 18 12.00
I intentionally skipped the 3rd (item/qty/value) set from 2nd record as it is possible it could be empty.
I would appreciate any feedback on how to resolve this issue. BTW, I am using SQL2005.
When I have this straigthened , it will serve as my dataset for a SSRS report.
Thanks in advance.
TP
Are these in a table or in a file? Also, you'd get a lot more bites on your question if you'd post the data in a readily consumable format. Please see the first link in my signature below for how to do that.
--Jeff Moden
November 13, 2009 at 4:59 am
November 13, 2009 at 6:19 am
Had your table been normalised ,u wouldn't have run into this problem.
One way that i can think of is by using a cursor on your table.
Create a cursor on your table and get the 10 column values into variables.
Create a temporary table with 4 columns n insert the required values into it.
Finally select * from the temporary table.
Can't think of a better solution to it.
November 13, 2009 at 8:51 am
For Convenience, i have also taken 3 sets for Item/Qty/Value..Is this the solution you are looking for?
I have created test table with some dummy data...
use tempdb
if object_id('tempdb..#testsplit') is not null
drop table #testsplit
create table #testsplit(SNO int,
Item1 varchar(10),qty1 int,value1 int,
Item2 varchar(10),qty2 int,value2 int,
Item3 varchar(10),qty3 int,value3 int)
insert into #testsplit values(1,'a',10,100,'b',20,200,'c',30,300)
insert into #testsplit values(2,'d',40,400,'e',50,500,'f',60,600)
insert into #testsplit values(3,'g',70,700,'h',80,800,'i',90,900)
select SNo,Item1,Qty1,Value1 from #testsplit
union all
select SNo,Item2,Qty2,Value2 from #testsplit
union all
select SNo,Item3,Qty3,Value3 from #testsplit
order by SNo
November 13, 2009 at 9:09 am
This will work. It's so amazing, what seemed to me so huge you made it so easy 🙂
Thanks Much,
TP
November 13, 2009 at 9:12 am
You welcome..
November 13, 2009 at 10:52 am
Although the solution provided by Jus is simple and straight forward, it might lead to a performance problem. With eight triples of columns it leads to eight table scan. The thread I mentioned in my previous post was just addressing that problem. A faster solution looks like the following (thanks Jus for providing the initial test data):
use tempdb
if object_id('tempdb..#testsplit') is not null
drop table #testsplit
create table #testsplit
(
SNO int primary key,
Item1 varchar(10),qty1 int,value1 int,
Item2 varchar(10),qty2 int,value2 int,
Item3 varchar(10),qty3 int,value3 int,
Item4 varchar(10),qty4 int,value4 int,
Item5 varchar(10),qty5 int,value5 int,
Item6 varchar(10),qty6 int,value6 int,
Item7 varchar(10),qty7 int,value7 int,
Item8 varchar(10),qty8 int,value8 int
)
insert into #testsplit(SNO,
Item1, qty1, value1,
Item2, qty2, value2,
Item3, qty3, value3,
Item4, qty4, value4,
Item5, qty5, value5,
Item6, qty6, value6
)
values(1,'a',10,100,'b',20,200,'c',30,300,'x',15,125,'y',63,133,'z',31,255)
insert into #testsplit(SNO,
Item1, qty1, value1,
Item2, qty2, value2,
Item3, qty3, value3,
Item4, qty4, value4,
Item5, qty5, value5
) values(2,'d',40,400,'e',50,500,'f',60,600,'u',100,750,'v',75,900)
insert into #testsplit(SNO,
Item1, qty1, value1,
Item2, qty2, value2,
Item3, qty3, value3
) values(3,'g',70,700,'h',80,800,'i',90,900)
select
u.SNo, n.Item, n.qty, n.Value
from
#testsplit u
cross apply
(
select Item1 as Item, qty1 as qty, Value1 as Value where Item1 is not null and qty1 is not null and Value1 is not null
union all
select Item2 as Item, qty2 as qty, Value2 as Value where Item2 is not null and qty2 is not null and Value2 is not null
union all
select Item3 as Item, qty3 as qty, Value3 as Value where Item3 is not null and qty3 is not null and Value3 is not null
union all
select Item4 as Item, qty4 as qty, Value4 as Value where Item4 is not null and qty4 is not null and Value4 is not null
union all
select Item5 as Item, qty5 as qty, Value5 as Value where Item5 is not null and qty5 is not null and Value5 is not null
union all
select Item6 as Item, qty6 as qty, Value6 as Value where Item6 is not null and qty6 is not null and Value6 is not null
union all
select Item7 as Item, qty7 as qty, Value7 as Value where Item7 is not null and qty7 is not null and Value7 is not null
union all
select Item8 as Item, qty8 as qty, Value8 as Value where Item8 is not null and qty8 is not null and Value8 is not null
) n
order by
1
It scans the table just once and splits up every row into at most eight triples.
Peter
November 13, 2009 at 12:04 pm
I got you..Thanks much...
Jus
November 13, 2009 at 12:09 pm
Perfect !! I tried both and I do see a difference in performance.
Thanks for the input much appreciated.
Thanks.
Tony
November 13, 2009 at 12:29 pm
Just a tiny change to Peters excellent sample:
I moved the WHERE clause from the UNION ALL part to the outer part of the query.
Performance seems to be the same (query plan shows that the filter part is at a different position).
It helps readability, I think. But I'm not sure if it will scale much different than Peters original solution.
Again, Peter: excellent solution!!
cross apply
(
select Item1 as Item, qty1 as qty, Value1 as Value
union all
select Item2 as Item, qty2 as qty, Value2 as Value
union all
select Item3 as Item, qty3 as qty, Value3 as Value
union all
select Item4 as Item, qty4 as qty, Value4 as Value
union all
select Item5 as Item, qty5 as qty, Value5 as Value
union all
select Item6 as Item, qty6 as qty, Value6 as Value
union all
select Item7 as Item, qty7 as qty, Value7 as Value
union all
select Item8 as Item, qty8 as qty, Value8 as Value
) n
WHERE ISNULL(n.qty + n.Value,Item) IS NOT NULL
order by
1
November 13, 2009 at 12:58 pm
I did that test also. It requires less coding but, based on the 3 rows input, there are less rows generated within the cross apply to join with if you put the null tests inside the cross apply. With 3 rows of input it's hard to measure any difference in performance. I leave it as an exercise to the OP to prove otherwise.
November 13, 2009 at 1:46 pm
Peter Brinkhaus (11/13/2009)
I did that test also. It requires less coding but, based on the 3 rows input, there are less rows generated within the cross apply to join with if you put the null tests inside the cross apply. With 3 rows of input it's hard to measure any difference in performance. I leave it as an exercise to the OP to prove otherwise.
I absolutely agree.
Just a thought: do you know if there's already an article describing the technique you used? If not I think you should write one... What do you think?
November 13, 2009 at 2:13 pm
lmu92 (11/13/2009)
Peter Brinkhaus (11/13/2009)
I did that test also. It requires less coding but, based on the 3 rows input, there are less rows generated within the cross apply to join with if you put the null tests inside the cross apply. With 3 rows of input it's hard to measure any difference in performance. I leave it as an exercise to the OP to prove otherwise.I absolutely agree.
Just a thought: do you know if there's already an article describing the technique you used? If not I think you should write one... What do you think?
In the thread http://qa.sqlservercentral.com/Forums/Topic809640-338-1.aspx I mentioned before there was already another person suggesting to write an article about this. So for now I leave it up to him.
Peter
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply