September 25, 2015 at 8:04 am
Hi all,
I have been away from SQL for a couple of months, and for the life of me cannot work out a simple way to do what i want to do. Please help!
select bu1.name,bu2.name, bu3.name, bu4.name, bu5.name
from
Sale
INNER JOIN
Sysuser on sale.userid = sysuser.userid
INNER JOIN businessunitBU1
on BU1.Businessunitid = sysuser.businessunitid
left JOIN businessunitBU2
on BU2.Businessunitid = bu1.parentbusinessunitid
left JOIN businessunitBU3
on BU3.Businessunitid = bu2.parentbusinessunitid
left JOIN businessunitBU4
on BU4.Businessunitid = bu3.parentbusinessunitid
left JOIN businessunitBU5
on BU5.Businessunitid = bu4.parentbusinessunitid
So effectively i am trying to go through the hierarchy to find all the levels. Unforunately not every entry has the same amount of levels.
So its coming out like this.....
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
So whats the easiest way to move everything along to fill in the blanks, but move the blanks to the end - so it would be more like this:
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
Any suggestions welcome
Dan
September 25, 2015 at 8:08 am
danielfountain (9/25/2015)
Hi all,I have been away from SQL for a couple of months, and for the life of me cannot work out a simple way to do what i want to do. Please help!
select bu1.name,bu2.name, bu3.name, bu4.name, bu5.name
from
Sale
INNER JOIN
Sysuser on sale.userid = sysuser.userid
INNER JOIN businessunitBU1
on BU1.Businessunitid = sysuser.businessunitid
left JOIN businessunitBU2
on BU2.Businessunitid = bu1.parentbusinessunitid
left JOIN businessunitBU3
on BU3.Businessunitid = bu2.parentbusinessunitid
left JOIN businessunitBU4
on BU4.Businessunitid = bu3.parentbusinessunitid
left JOIN businessunitBU5
on BU5.Businessunitid = bu4.parentbusinessunitid
So effectively i am trying to go through the hierarchy to find all the levels. Unforunately not every entry has the same amount of levels.
So its coming out like this.....
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
So whats the easiest way to move everything along to fill in the blanks, but move the blanks to the end - so it would be more like this:
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
Any suggestions welcome
Dan
You could use a series of ISNULL for every column. I would make a strong argument that is not a good idea. You would be putting values from BU1 into the column named BU3 which means you now have no idea what value is what or where they actually came from.
_______________________________________________________________
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/
September 25, 2015 at 8:26 am
I'm not sure if this is a good idea:
WITH rCTE AS(
SELECT s.saleid, u.userid, b.businessunitid, b.name, b.parentbusinessunitid, 1 AS n
from Sale s
INNER JOIN Sysuser u on s.userid = u.userid
INNER JOIN businessunit b on u.Businessunitid = b.businessunitid
UNION ALL
SELECT r.saleid, r.userid, b.businessunitid, b.name, b.parentbusinessunitid, n + 1
from rCTE r
INNER JOIN businessunit b on r.parentbusinessunitid = b.businessunitid
),
RowNums AS(
SELECT r.saleid, r.userid, r.name, ROW_NUMBER() OVER( PARTITION BY r.saleid ORDER BY r.n DESC) rn
from rCTE r
)
SELECT MAX(CASE WHEN rn = 5 THEN name END) AS BU1,
MAX(CASE WHEN rn = 4 THEN name END) AS BU2,
MAX(CASE WHEN rn = 3 THEN name END) AS BU3,
MAX(CASE WHEN rn = 2 THEN name END) AS BU4,
MAX(CASE WHEN rn = 1 THEN name END) AS BU5
FROM RowNums
GROUP BY saleid
ORDER BY saleid;
September 25, 2015 at 9:17 am
Sean Lange (9/25/2015)
danielfountain (9/25/2015)
Hi all,I have been away from SQL for a couple of months, and for the life of me cannot work out a simple way to do what i want to do. Please help!
select bu1.name,bu2.name, bu3.name, bu4.name, bu5.name
from
Sale
INNER JOIN
Sysuser on sale.userid = sysuser.userid
INNER JOIN businessunitBU1
on BU1.Businessunitid = sysuser.businessunitid
left JOIN businessunitBU2
on BU2.Businessunitid = bu1.parentbusinessunitid
left JOIN businessunitBU3
on BU3.Businessunitid = bu2.parentbusinessunitid
left JOIN businessunitBU4
on BU4.Businessunitid = bu3.parentbusinessunitid
left JOIN businessunitBU5
on BU5.Businessunitid = bu4.parentbusinessunitid
So effectively i am trying to go through the hierarchy to find all the levels. Unforunately not every entry has the same amount of levels.
So its coming out like this.....
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
So whats the easiest way to move everything along to fill in the blanks, but move the blanks to the end - so it would be more like this:
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
Any suggestions welcome
Dan
You could use a series of ISNULL for every column. I would make a strong argument that is not a good idea. You would be putting values from BU1 into the column named BU3 which means you now have no idea what value is what or where they actually came from.
Where they come from isnt where they should be - so its irrelevent. Isnull - i cant see how this would work. It would work ok for BU5 but what about 4?
September 25, 2015 at 9:23 am
Luis Cazares (9/25/2015)
I'm not sure if this is a good idea:
WITH rCTE AS(
SELECT s.saleid, u.userid, b.businessunitid, b.name, b.parentbusinessunitid, 1 AS n
from Sale s
INNER JOIN Sysuser u on s.userid = u.userid
INNER JOIN businessunit b on u.Businessunitid = b.businessunitid
UNION ALL
SELECT r.saleid, r.userid, b.businessunitid, b.name, b.parentbusinessunitid, n + 1
from rCTE r
INNER JOIN businessunit b on r.parentbusinessunitid = b.businessunitid
),
RowNums AS(
SELECT r.saleid, r.userid, r.name, ROW_NUMBER() OVER( PARTITION BY r.saleid ORDER BY r.n DESC) rn
from rCTE r
)
SELECT MAX(CASE WHEN rn = 5 THEN name END) AS BU1,
MAX(CASE WHEN rn = 4 THEN name END) AS BU2,
MAX(CASE WHEN rn = 3 THEN name END) AS BU3,
MAX(CASE WHEN rn = 2 THEN name END) AS BU4,
MAX(CASE WHEN rn = 1 THEN name END) AS BU5
FROM RowNums
GROUP BY saleid
ORDER BY saleid;
However i do think this would work.... VERY slowly though. I keep feeling there is something i am missing!!
September 25, 2015 at 9:27 am
danielfountain (9/25/2015)
Luis Cazares (9/25/2015)
I'm not sure if this is a good idea:However i do think this would work.... VERY slowly though. I keep feeling there is something i am missing!!
You're probably right about the bad performance. The problem is that there's no great option for this kind of queries.
September 25, 2015 at 9:36 am
Luis Cazares (9/25/2015)
danielfountain (9/25/2015)
Luis Cazares (9/25/2015)
I'm not sure if this is a good idea:However i do think this would work.... VERY slowly though. I keep feeling there is something i am missing!!
You're probably right about the bad performance. The problem is that there's no great option for this kind of queries.
I keep wondering if i can do something with concat them together then split them apart again.....
September 25, 2015 at 10:02 am
danielfountain (9/25/2015)
Sean Lange (9/25/2015)
danielfountain (9/25/2015)
Hi all,I have been away from SQL for a couple of months, and for the life of me cannot work out a simple way to do what i want to do. Please help!
select bu1.name,bu2.name, bu3.name, bu4.name, bu5.name
from
Sale
INNER JOIN
Sysuser on sale.userid = sysuser.userid
INNER JOIN businessunitBU1
on BU1.Businessunitid = sysuser.businessunitid
left JOIN businessunitBU2
on BU2.Businessunitid = bu1.parentbusinessunitid
left JOIN businessunitBU3
on BU3.Businessunitid = bu2.parentbusinessunitid
left JOIN businessunitBU4
on BU4.Businessunitid = bu3.parentbusinessunitid
left JOIN businessunitBU5
on BU5.Businessunitid = bu4.parentbusinessunitid
So effectively i am trying to go through the hierarchy to find all the levels. Unforunately not every entry has the same amount of levels.
So its coming out like this.....
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
So whats the easiest way to move everything along to fill in the blanks, but move the blanks to the end - so it would be more like this:
Bu1 BU2 BU3 BU4 BU5
L5 L4 L3 L2 L1
L4 L3 L2 L1
L3 L2 L1
L5 L4 L3 L2 L1
Any suggestions welcome
Dan
You could use a series of ISNULL for every column. I would make a strong argument that is not a good idea. You would be putting values from BU1 into the column named BU3 which means you now have no idea what value is what or where they actually came from.
Where they come from isnt where they should be - so its irrelevent. Isnull - i cant see how this would work. It would work ok for BU5 but what about 4?
It was the first thing that popped into my head. COALESCE would probably be better. I still think this is a bit strange and performance is likely going to be a challenge given all the joins to the same table.
select COALESCE(bu1.name,bu2.name, bu3.name, bu4.name, bu5.name) as bu1
COALESCE(bu2.name, bu3.name, bu4.name, bu5.name) as bu2
COALESCE(bu3.name, bu4.name, bu5.name) as bu3
COALESCE(bu4.name, bu5.name) as bu4
bu5.name as bu5
from
Sale
INNER JOIN
Sysuser on sale.userid = sysuser.userid
INNER JOIN businessunitBU1
on BU1.Businessunitid = sysuser.businessunitid
left JOIN businessunitBU2
on BU2.Businessunitid = bu1.parentbusinessunitid
left JOIN businessunitBU3
on BU3.Businessunitid = bu2.parentbusinessunitid
left JOIN businessunitBU4
on BU4.Businessunitid = bu3.parentbusinessunitid
left JOIN businessunitBU5
on BU5.Businessunitid = bu4.parentbusinessunitid
_______________________________________________________________
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/
September 25, 2015 at 10:05 am
Thanks for everyones help.
I worked out a quick way to do it.
So effectively i numbered the columns 1-5 the opposite way round. Then in unpivoted the data making one column saying 1-5. So some of these would have had 3,4, and 5 for one salesid.
Then i addedin in a row_num using this column partitioned by the salesid - to turn 1,2 and 3.
THEN.... i pivot back using the row_num and bingo!!!!!
Thanks again all for the options - definitely got me thinking.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply