October 17, 2016 at 11:19 pm
Hi Guys,
I have two tables structured like this:
tblStructure
=================
[VCode].....[Name]
1...............WBS1
2...............WBS2
3...............WBS3
tblItems
=================
[VCode]..........[Name]..........[StructureVCode]
1.....................Item1............1
2.....................Item2............1
3.....................Item3............2
4.....................Item4............2
5.....................Item5............2
Can anybody help me producing this table, using T-SQL Please?:
[VCode]..........[Name]
S1...................WBS1
I1........................| Item1
I2........................| Item2
S2...................WBS2
I3........................| Item3
I4........................| Item4
I5........................| Item5
S3...................WBS3
Thanks,
October 18, 2016 at 3:27 am
When posting, it's always best to supply your DDL and INSERT statements (see my link to Jeff's post in my signature).
Hopefully, however, this meets your goals:
CREATE TABLE #Structure (VCode INT IDENTITY(1,1),
[Name] VARCHAR(4));
CREATE TABLE #Item (VCode INT IDENTITY (1,1),
[Name] VARCHAR(8),
StructureVCode INT);
INSERT INTO #Structure ([Name])
VALUES ('WBS1'),
('WBS2'),
('WBS3');
INSERT INTO #Item ([Name], [StructureVCode])
VALUES ('Item1', 1),
('Item2', 1),
('Item3', 2),
('Item4', 2),
('Item5', 2);
SELECT T.Prefix + CASE T.Prefix
WHEN 'S' THEN CAST(T.StructureVCode AS VARCHAR(4))
WHEN 'I' THEN CAST(T.ItemVCode AS VARCHAR(4))
END AS VCode,
T.[Name]
FROM (
SELECT 'S' as Prefix,
0 AS ItemVCode,
S. VCode AS StructureVCode,
S.[Name]
FROM #Structure S
UNION
SELECT 'I' AS Prefix,
I.VCode AS ItemVCode,
S.VCode AS StructureVCode,
I.[Name]
FROM #Structure S
JOIN #Item I ON S.VCode = I.StructureVCode
) T
ORDER BY T.StructureVCode,
T.ItemVCode;
DROP TABLE #Structure;
DROP TABLE #Item;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 18, 2016 at 4:56 pm
Thank you very much Thom,
That was great. You solved my week problem!
It was very smart. Thanks again
October 18, 2016 at 6:37 pm
I came back to say thank you again!!!
October 18, 2016 at 6:58 pm
Since this appears to be for a WBS structure, will you have additional levels than the two that you currently have?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy