March 11, 2013 at 2:28 am
I have the following table in SQL Server 2008
DECLARE @UnitConvert table
(
ID int identity(1,1),
ConvertUnitOne nvarchar(50),
ConvertUnitTwo nvarchar(50)
)
INSERT INTO @UnitConvert
SELECT 100,500
UNION ALL SELECT 200,100
UNION ALL SELECT 500,300
UNION ALL SELECT 2000,1000
UNION ALL SELECT 3000,9000
UNION ALL SELECT 2000,700
UNION ALL SELECT 820,3000
SELECT * FROM @UnitConvert
Here value in UnitConvertOne is equivalent to UnitConvertTwo So it has a chain of value linking
100 = 500,200=100,500=300..so 100,200,300,500 make a group
So i want to display the result like
Group unit
1 100
200
300
500
2 700
1000
2000
3 820
3000
9000
Group value will be autoincrement based on the number of groups can be created, Unit value can be sorted from small to large value
March 11, 2013 at 3:48 am
I have a question about group #2. Shouldn't it actually be two groups:
Group unit
1 100
200
300
500
2 700
1000
2000
3 820
3000
9000
I don't see any relationship between 820, 3000 and 9000 such that they'd appear in group 2 (700, 1000, 2000).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2013 at 3:53 am
dwain.c (3/11/2013)
I have a question about group #2. Shouldn't it actually be two groups:
Group unit
1 100
200
300
500
2 700
1000
2000
3 820
3000
9000
I don't see any relationship between 820, 3000 and 9000 such that they'd appear in group 2 (700, 1000, 2000).
Thanks dwain for pointing out the mistake..
March 11, 2013 at 4:09 am
UNION ALL SELECT 2000,1000
UNION ALL SELECT 2000,700
doesn't look like a chain to me, is it valid combination? Can ConvertUnitOne point to two different ConvertUnitTwo?
If it's not valid, than you can use the following query
(I've changed UNION ALL SELECT 2000,700 to UNION ALL SELECT 1000,820):
DECLARE @UnitConvert table
(
ID int identity(1,1),
ConvertUnitOne nvarchar(50),
ConvertUnitTwo nvarchar(50)
)
INSERT INTO @UnitConvert
SELECT 100,500
UNION ALL SELECT 200,100
UNION ALL SELECT 500,300
UNION ALL SELECT 2000,1000
UNION ALL SELECT 3000,9000
UNION ALL SELECT 1000,820
UNION ALL SELECT 820,3000
;WITH cteUP AS
(
SELECT ConvertUnitOne AS childUP, ConvertUnitTwo AS unitUP, 0 AS Lvl
FROM @UnitConvert
UNION ALL
SELECT cte.childUP, u.ConvertUnitTwo AS unitUP, Lvl = Lvl + 1
FROM @UnitConvert u
INNER JOIN cteUP cte ON cte.unitUP = u.ConvertUnitOne
)
select c.ID, c.ConvertUnitOne, c.ConvertUnitTwo
,dense_rank() OVER (ORDER BY cm.unitUP) AS GrpNO
from @UnitConvert c
cross apply (select TOP 1 unitUP from cteUP m where
m.childUP = c.ConvertUnitOne order by Lvl desc) cm
And, if the data you've supplied is valid (no-chain one), then the above query will split it into three groups.
March 11, 2013 at 4:18 am
dwain.c (3/11/2013)
I have a question about group #2. Shouldn't it actually be two groups:
Group unit
1 100
200
300
500
2 700
1000
2000
3 820
3000
9000
I don't see any relationship between 820, 3000 and 9000 such that they'd appear in group 2 (700, 1000, 2000).
How embarrassing! The issue I believe is group #2. To make it work I believe you're going to need to reverse either the 700 or the 1000 values in one of the rows where they uniquely appear in order to form a hierarchical chain.
I think...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2013 at 4:21 am
Of course, I could be wrong as Eugene has proven in the past.
I think his query may be workable. All you'd need to do is UNPIVOT the two columns. You may want to look at the first link in my signature area on a way to do that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2013 at 4:25 am
dwain.c (3/11/2013)
Of course, I could be wrong as Eugene has proven in the past.I think his query may be workable. All you'd need to do is UNPIVOT the two columns. You may want to look at the first link in my signature area on a way to do that.
I'm not sure now, as OP has changed his requirements in his first post...
I guess my query need a small fix now. Saying that, it's quite strange to have the same ConvertUnitOne against two different ConvertUnitTwo...
March 11, 2013 at 4:37 am
Ok, if the data and output requirements OP provided this time are right, then the following should work for him:
DECLARE @UnitConvert table
(
ID int identity(1,1),
ConvertUnitOne nvarchar(50),
ConvertUnitTwo nvarchar(50)
)
INSERT INTO @UnitConvert
SELECT 100,500
UNION ALL SELECT 200,100
UNION ALL SELECT 500,300
UNION ALL SELECT 2000,1000
UNION ALL SELECT 3000,9000
UNION ALL SELECT 2000,700
UNION ALL SELECT 820,3000
;WITH cteUP AS
(
SELECT ConvertUnitTwo AS childUP, ConvertUnitOne AS unitUP, 0 AS Lvl
FROM @UnitConvert
UNION ALL
SELECT cte.childUP, u.ConvertUnitOne AS unitUP, Lvl = Lvl + 1
FROM @UnitConvert u
INNER JOIN cteUP cte ON cte.unitUP = u.ConvertUnitTwo
)
--select * from cteUP
SELECT c.ConvertUnit
,DENSE_RANK() OVER (ORDER BY ISNULL(cm.unitUP, c.ConvertUnit)) AS GrpNO
FROM (SELECT ConvertUnitOne AS ConvertUnit FROM @UnitConvert
UNION
SELECT ConvertUnitTwo AS ConvertUnit FROM @UnitConvert) c
OUTER APPLY (SELECT TOP 1 unitUP FROM cteUP m WHERE
m.childUP = c.ConvertUnit ORDER BY Lvl DESC) cm
March 11, 2013 at 5:30 am
Thanks a lot eugene..this is what i actually want
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply