October 18, 2016 at 12:44 am
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
October 18, 2016 at 5:12 am
--DROP TABLE #tblStructure;
--DROP TABLE #tblItems;
CREATE TABLE #tblStructure (
VCode INT, [Name] VARCHAR(255)
)
CREATE TABLE #tblItems (
VCode INT, [Name] VARCHAR(255), StuctureVCode INT
)
INSERT INTO #tblStructure
( VCode, Name )
VALUES
( 1,'WBS1')
,( 2,'WBS2')
,( 3,'WBS3');
INSERT INTO #tblItems
( VCode, Name, StuctureVCode )
VALUES
(1,'Item1', 1),
(2,'Item2', 1),
(3,'Item3', 2),
(4,'Item4', 2),
(5,'Item5', 2)
SELECT
CHOOSE(x.Level,'S','I') + CONVERT(VARCHAR,x.StuctureVCode) AS Vcode
, CHOOSE(x.Level, [Name], REPLICATE(' ',Level) + '| ' + Name) AS [Name]
FROM
(
SELECT '1' AS Level, VCode AS StuctureVCode, NULL AS Vcode, Name FROM #tblStructure
UNION ALL
SELECT '2' AS Level, StuctureVCode, VCode, Name FROM #tblItems
) AS x
ORDER BY x.StuctureVCode, x.Vcode
October 18, 2016 at 5:24 am
This is a duplicate of http://www.sqlservercentral.com/Forums/Topic1826799-3740-1.aspx.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 18, 2016 at 4:55 pm
WOW! Thanks Spiff. That was very smart.
thanks again, you solved my problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply