November 12, 2018 at 10:42 am
I have a table that lists linked warehouse locations. The previous design was to link each location id to every other location in it's set. I want to convert this to a master location id and child ids for each set. So for example, the present layout for 3 linked locations (27, 45, 50) is (I know - bad design):
27,45
27,50
45,27
45,50
50,27
50,45
The result I want is master id, child id
27,45
27,50
27,27
I have tries several approaches, but can't seem to get the desired results. I have provided a sample dataset below
INSERT @LocationLinks -- set 1 (27,45,59, 50) ; set 2 (34,61)
SELECT '27', '45' UNION ALL
SELECT '27', '50' UNION ALL
SELECT '27', '59' UNION ALL
SELECT '34', '61' UNION ALL
SELECT '45', '27' UNION ALL
SELECT '45', '50' UNION ALL
SELECT '45', '59' UNION ALL
SELECT '50', '27' UNION ALL
SELECT '50', '45' UNION ALL
SELECT '50', '59' UNION ALL
SELECT '59', '27' UNION ALL
SELECT '59', '45' UNION ALL
SELECT '59', '50' UNION ALL
SELECT '61', '34'
TIA
John Deupree
November 12, 2018 at 2:46 pm
Why do this? What's the bigger picture?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 12, 2018 at 3:51 pm
sgmunson - Monday, November 12, 2018 2:46 PMWhy do this? What's the bigger picture?
We want to implement a master location and make location set maintenance easier. We want to limit certain functionality to only be done from the master location. The goal is to create a master location table and a linked location (child) table.
I have figured out a way to do this using a cursor but I thought it was an interesting problem.
John Deupree
November 13, 2018 at 5:13 am
Why doesn't it come up with:
45,27
45,50
45,45
?
Can you provide your cursor solution so we can convert it to a set solution?
November 13, 2018 at 8:29 am
Jonathan AC Roberts - Tuesday, November 13, 2018 5:13 AMWhy doesn't it come up with:
45,27
45,50
45,45
?
Can you provide your cursor solution so we can convert it to a set solution?
DECLARE @LocationLinks TABLE
(
ID INT IDENTITY
, LocationId INT
, LinkLocationId INT
);
DECLARE @LocationLinks2 TABLE
(
LocationId INT
, LinkLocationId INT
);
DECLARE @MasterLocation TABLE
(
ID INT IDENTITY
, LocationId INT
);
INSERT @LocationLinks --27,45,59, 50
SELECT '27'
, '45'
UNION ALL
SELECT '27'
, '50'
UNION ALL
SELECT '27'
, '59'
UNION ALL
SELECT '34'
, '61'
UNION ALL
SELECT '45'
, '27'
UNION ALL
SELECT '45'
, '50'
UNION ALL
SELECT '45'
, '59'
UNION ALL
SELECT '50'
, '27'
UNION ALL
SELECT '50'
, '45'
UNION ALL
SELECT '50'
, '59'
UNION ALL
SELECT '59'
, '27'
UNION ALL
SELECT '59'
, '45'
UNION ALL
SELECT '59'
, '50'
UNION ALL
SELECT '61'
, '34';
/* declare variables */
DECLARE @Locationid INT
, @linkid INT;
DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR
SELECT LocationId
, LinkLocationId
FROM @LocationLinks;
OPEN csr;
FETCH NEXT FROM csr
INTO @Locationid
, @linkid;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @LocationLinks2
SELECT @Locationid
, @linkid
WHERE @linkid NOT IN(
SELECT LinkLocationId FROM @LocationLinks2
)
AND @linkid NOT IN(
SELECT LocationId FROM @LocationLinks2
);
FETCH NEXT FROM csr
INTO @Locationid
, @linkid;
END;
CLOSE csr;
DEALLOCATE csr;
-- optionally add link to master location
WITH cte
AS (SELECT DISTINCT LocationId
FROM @LocationLinks2)
INSERT @LocationLinks2
SELECT LocationId
, LocationId
FROM cte;
SELECT *
FROM @LocationLinks2
ORDER BY LocationId, LinkLocationId;
John Deupree
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply