Reputation: 43636
I am looking for a tricky way to build hierarchical order rows from the following data:
SET NOCOUNT ON
GO
DECLARE @DataSource TABLE
(
[Col001] CHAR(2)
,[Col002] CHAR(2)
,[Col003] CHAR(2)
,[Col004] CHAR(2)
)
INSERT INTO @DataSource ([Col001], [Col002], [Col003], [Col004])
VALUES ('A1','B1','C1','D1')
,('A1','B1','C1','D2')
,('A1','B1','C2','D3')
,('A1','B1','C2','D4')
,('A1','B2','C1','D5')
,('A1','B2','C1','D6')
,('A1','B2','C1','D7')
SELECT [Col001]
,[Col002]
,[Col003]
,[Col004]
FROM @DataSource
SET NOCOUNT OFF
GO
And the result I am looking is something like this:
I know the source data and the results may look strange, but how I am getting them. In my real situation, I may get multiple columns, but each suggestion or idea will be appreciated.
Note: The [Level] order needs to be the same in the screenshot that I have provided. That's the real difficulty.
The idea is to preserve the connections:
Upvotes: 2
Views: 95
Reputation: 24134
If the order is important then try this:
select [Level],Val from
(
SELECT '0' as [Level], [Col001] as Ord, [Col001] as Val FROM @DataSource
union
SELECT '1' as [Level], [Col001]+[Col002] as Ord, [Col002] as Val FROM @DataSource
union
SELECT '2' as [Level], [Col001]+[Col002]+[Col003] as Ord, [Col003] as Val FROM @DataSource
union
SELECT '3' as [Level], [Col001]+[Col002]+[Col003]+[Col004] as Ord, [Col004] as Val FROM @DataSource
) as T1
ORDER BY Ord,[Level]
Upvotes: 2
Reputation: 397
I am not certain how you wish to determine the level Maybe something like this
/*Union a select for each column and select distinct.
I am assuming that column number - 1 is the level*/
SELECT DISTINCT T1.Level,T1.Data
FROM (
SELECT 0 [Level],[Col001] [Data] FROM @DataSource
UNION ALL SELECT 1, [Col002] FROM @DataSource
UNION ALL SELECT 2, [Col003] FROM @DataSource
UNION ALL SELECT 3, [Col004] FROM @DataSource
) T1
Upvotes: 0