Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a Hierarchy


Creating a Hierarchy

Author
Message
ramos.ferdinand
ramos.ferdinand
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
Hi guys,

I'm trying to create an hierarchy using tsql. Below is a sample data:

Column 1 Column2 Column3 Column4
Base 16369 14499 NULL
NULL 14499 14498 Level 1
NULL 14498 14371 Level 2
NULL 14371 234 Level 3
NULL 234 225 Level 4
NULL 225 1 Level 5
NULL 1 0 Level 6

Notice Column3 is the child of the root(Base) but will be the parent of the next level.

The result I want to achieve is
Column1 Column2 Column3 Column4 Column5 Column6 Column7
Base Level 1 Level 2 Level 3 Level 4 Level 5 Level 6

or

Column1 Column2 Column3 Column4 Column5 Column6 Column7
Level 6 Level 5 Level 4 Level 3 Level 2 Level 1 Base

Any idea?

Thanks in advance.
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
ramos.ferdinand (10/16/2013)
Hi guys,

I'm trying to create an hierarchy using tsql. Below is a sample data:

Column 1 Column2 Column3 Column4
Base 16369 14499 NULL
NULL 14499 14498 Level 1
NULL 14498 14371 Level 2
NULL 14371 234 Level 3
NULL 234 225 Level 4
NULL 225 1 Level 5
NULL 1 0 Level 6

Notice Column3 is the child of the root(Base) but will be the parent of the next level.

The result I want to achieve is
Column1 Column2 Column3 Column4 Column5 Column6 Column7
Base Level 1 Level 2 Level 3 Level 4 Level 5 Level 6

or

Column1 Column2 Column3 Column4 Column5 Column6 Column7
Level 6 Level 5 Level 4 Level 3 Level 2 Level 1 Base

Any idea?

Thanks in advance.


I don't fully understand what you actually want here, it's always easier to figure out when you supply us with DDL, readily consumable sample data and expected results based on the sample data. Here's my shot in the dark.

First, this is the sample data I used to test with: -
-- Set up readily consumable sample data so that we can test any solution
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Column 1], [Column 2], [Column 3], [Column 4]
INTO #testEnvironment
FROM (VALUES('Base',16369,14499,NULL),
(NULL,14499,14498,'Level 1'),
(NULL,14498,14371,'Level 2'),
(NULL,14371,234,'Level 3'),
(NULL,234,225,'Level 4'),
(NULL,225,1,'Level 5'),
(NULL,1,0,'Level 6')
)a([Column 1], [Column 2], [Column 3], [Column 4]);



Here's my attempted solution: -
DECLARE @ORDER BIT = 0, @SQL NVARCHAR(MAX);

IF @ORDER = 0
BEGIN;
-- ORDER BASE TO LEAF
WITH CTE AS
(
SELECT [Column 1], [Column 2], [Column 3], ISNULL([Column 4],[Column 1]) AS [Column 4],
1 AS Pos
FROM #testEnvironment
WHERE [Column 1] = 'Base'
UNION ALL
SELECT a.[Column 1], a.[Column 2], a.[Column 3], a.[Column 4], Pos + 1
FROM #testEnvironment a
INNER JOIN CTE b ON b.[Column 3] = a.[Column 2]
)
SELECT @SQL = 'SELECT '+
STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+[Column 4]+CHAR(39)+' AS [Column '+CAST(Pos AS VARCHAR(3))+']'
FROM CTE
ORDER BY Pos ASC
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
);
END;
ELSE IF @ORDER = 1
BEGIN;
-- ORDER LEAF BASE
WITH CTE AS
(
SELECT [Column 1], [Column 2], [Column 3], ISNULL([Column 4],[Column 1]) AS [Column 4],
1 AS Pos
FROM #testEnvironment
WHERE [Column 1] = 'Base'
UNION ALL
SELECT a.[Column 1], a.[Column 2], a.[Column 3], a.[Column 4], Pos + 1
FROM #testEnvironment a
INNER JOIN CTE b ON b.[Column 3] = a.[Column 2]
)
SELECT @SQL = 'SELECT '+
STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+[Column 4]+CHAR(39)+' AS [Column '+CAST(Pos AS VARCHAR(3))+']'
FROM CTE
ORDER BY Pos DESC
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
);
END;

EXECUTE sp_executesql @SQL;



That results in: -
Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7
-------- -------- -------- -------- -------- -------- --------
Base Level 1 Level 2 Level 3 Level 4 Level 5 Level 6


If you change the @ORDER to 1, then it results in: -
Column 7 Column 6 Column 5 Column 4 Column 3 Column 2 Column 1
-------- -------- -------- -------- -------- -------- --------
Level 6 Level 5 Level 4 Level 3 Level 2 Level 1 Base



Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
ramos.ferdinand
ramos.ferdinand
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
Cadavre (10/16/2013)

I don't fully understand what you actually want here, it's always easier to figure out when you supply us with DDL, readily consumable sample data and expected results based on the sample data. Here's my shot in the dark.

First, this is the sample data I used to test with: -
-- Set up readily consumable sample data so that we can test any solution
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Column 1], [Column 2], [Column 3], [Column 4]
INTO #testEnvironment
FROM (VALUES('Base',16369,14499,NULL),
(NULL,14499,14498,'Level 1'),
(NULL,14498,14371,'Level 2'),
(NULL,14371,234,'Level 3'),
(NULL,234,225,'Level 4'),
(NULL,225,1,'Level 5'),
(NULL,1,0,'Level 6')
)a([Column 1], [Column 2], [Column 3], [Column 4]);



Here's my attempted solution: -
DECLARE @ORDER BIT = 0, @SQL NVARCHAR(MAX);

IF @ORDER = 0
BEGIN;
-- ORDER BASE TO LEAF
WITH CTE AS
(
SELECT [Column 1], [Column 2], [Column 3], ISNULL([Column 4],[Column 1]) AS [Column 4],
1 AS Pos
FROM #testEnvironment
WHERE [Column 1] = 'Base'
UNION ALL
SELECT a.[Column 1], a.[Column 2], a.[Column 3], a.[Column 4], Pos + 1
FROM #testEnvironment a
INNER JOIN CTE b ON b.[Column 3] = a.[Column 2]
)
SELECT @SQL = 'SELECT '+
STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+[Column 4]+CHAR(39)+' AS [Column '+CAST(Pos AS VARCHAR(3))+']'
FROM CTE
ORDER BY Pos ASC
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
);
END;
ELSE IF @ORDER = 1
BEGIN;
-- ORDER LEAF BASE
WITH CTE AS
(
SELECT [Column 1], [Column 2], [Column 3], ISNULL([Column 4],[Column 1]) AS [Column 4],
1 AS Pos
FROM #testEnvironment
WHERE [Column 1] = 'Base'
UNION ALL
SELECT a.[Column 1], a.[Column 2], a.[Column 3], a.[Column 4], Pos + 1
FROM #testEnvironment a
INNER JOIN CTE b ON b.[Column 3] = a.[Column 2]
)
SELECT @SQL = 'SELECT '+
STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+[Column 4]+CHAR(39)+' AS [Column '+CAST(Pos AS VARCHAR(3))+']'
FROM CTE
ORDER BY Pos DESC
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
);
END;

EXECUTE sp_executesql @SQL;



That results in: -
Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7
-------- -------- -------- -------- -------- -------- --------
Base Level 1 Level 2 Level 3 Level 4 Level 5 Level 6


If you change the @ORDER to 1, then it results in: -
Column 7 Column 6 Column 5 Column 4 Column 3 Column 2 Column 1
-------- -------- -------- -------- -------- -------- --------
Level 6 Level 5 Level 4 Level 3 Level 2 Level 1 Base


Sorry guys if my message was not that clear and havent put the DDL. Sad

Cadavre, yes you hit it right! I wanted the result you've posted. But the solution will work for a single record. What if I wanted it on a multiple records?

Change the value of the record to make it more understandable.

DDL:


IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,234,'2013'),
(NULL,234,6199,'Diesel'),
(NULL,6199,1,'SUV'),
(NULL,1,0,'Honda')
)a([Car], [Child], [Parent], [Category_Name]);



The result I wanted is:
Car Type Category 1 Category 2 Category 3 Category 4 Category 5 Category6
Type R Civic Manual Transmission 2013 Gasoline Pasenger Car Honda
SX 2.0 CRV Automatic Transmission 2013 Diesel SUV Honda

Basically the records with "NULL" value on [Car] column are the categories and the records with "NULL" values on [Category_Name] are the root.
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
ramos.ferdinand (10/17/2013)
Sorry guys if my message was not that clear and havent put the DDL. Sad

Cadavre, yes you hit it right! I wanted the result you've posted. But the solution will work for a single record. What if I wanted it on a multiple records?

Change the value of the record to make it more understandable.

DDL:


IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,234,'2013'),
(NULL,234,6199,'Diesel'),
(NULL,6199,1,'SUV'),
(NULL,1,0,'Honda')
)a([Car], [Child], [Parent], [Category_Name]);



The result I wanted is:
Car Type Category 1 Category 2 Category 3 Category 4 Category 5 Category6
Type R Civic Manual Transmission 2013 Gasoline Pasenger Car Honda
SX 2.0 CRV Automatic Transmission 2013 Diesel SUV Honda

Basically the records with "NULL" value on [Car] column are the categories and the records with "NULL" values on [Category_Name] are the root.


There's an error with your hierarchy. Several of your nodes are pointing at multiple areas, which means that they are valid children of both cars. I've changed your hierarchy to this: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,235,'2013'),
(NULL,235,6199,'Diesel'),
(NULL,6199,1,'SUV')
)a([Car], [Child], [Parent], [Category_Name]);



DECLARE @SQL NVARCHAR(MAX);

WITH CTE AS
(
SELECT [Car], [Child], [Parent], ISNULL([Category_Name],[Car]) AS [Category_Name],
1 AS [Pos]
FROM #testEnvironment
WHERE [Car] IS NOT NULL
UNION ALL
SELECT ISNULL(a.[Car],b.[Car]), a.[Child], a.[Parent], a.[Category_Name], [Pos] + 1
FROM #testEnvironment a
INNER JOIN CTE b ON b.[Parent] = a.[Child]
)
SELECT @SQL =
STUFF((SELECT CHAR(13)+CHAR(10)+'UNION ALL SELECT '+Dyn
FROM (SELECT [Car]
FROM CTE
GROUP BY [Car]
)a
CROSS APPLY (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+b.[Category_Name]+CHAR(39)+ ' AS ' +
CASE WHEN b.[Car] = b.[Category_Name]
THEN '[Car Type]'
ELSE '[Category ' +CAST(b.[Pos] AS VARCHAR(3))+']' END
FROM CTE b
WHERE a.[Car] = b.[Car]
ORDER BY [Car], [Pos]
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
)
)ca(Dyn)
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,12,''
);

EXECUTE sp_executesql @SQL;



The above produces: -
Car Type Category 2 Category 3             Category 4 Category 5 Category 6    Category 7
-------- ---------- ---------------------- ---------- ---------- ------------- ----------
SX 2.0 CRV Automatic Transmission 2013 Diesel SUV Honda
Type R Civic Manual Transmission 2013 Gasoline Passenger Car Honda


I've left a deliberate bug in there for you to try and resolve. What happens if there are different numbers of categories for each car?

IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,235,'2013'),
(NULL,235,6199,'Diesel'),
(NULL,6199,1,'SUV'),
('SX 3.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Manual Transmission'),
(NULL,6264,6199,'2013'),
(NULL,6199,1,'SUV')
)a([Car], [Child], [Parent], [Category_Name]);



Note, the SX 3.0 has only 6 categories.

If you execute the code I've supplied again, you get: -
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.


Have a go at solving this yourself. If you run into difficulties, post back with what you've tried and I'll help walk you through the solution.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
ramos.ferdinand
ramos.ferdinand
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
Sorry I know it's spoon feeding already. I've exerted all efforts but cant find the right script. I was thinking to put a "NULL" value to the columns that lacks so that i could be able to union. but the problem is i dont know where. Sick

this is the part that i dont really understand. specially to the part where you put ".value". if you could walk me through on what it does that would be a GREAT HELP.

STUFF((SELECT CHAR(13)+CHAR(10)+'Union All SELECT '+Dyn
FROM (SELECT [book]
FROM CTE
GROUP BY [book]
)a
CROSS APPLY (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+ b.[Category_Name]+CHAR(39)+ ' AS ' +
CASE WHEN b.[book] = b.[Category_Name] THEN '[Book]'
ELSE '[Level ' +CAST(b.[Pos] AS VARCHAR(3))+']'
END
FROM CTE b
WHERE a.[book] = b.[book]
ORDER BY [book], [Pos]
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
)
)ca(Dyn)
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,12,''
);

dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 6431
ramos.ferdinand (10/17/2013)

DDL:


IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;

SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,234,'2013'),
(NULL,234,6199,'Diesel'),
(NULL,6199,1,'SUV'),
(NULL,1,0,'Honda')
)a([Car], [Child], [Parent], [Category_Name]);





Using the above sample data, you can unravel the hierarchy using a recursive CTE and then do a cross tab query to PIVOT.


WITH UnravelHiearchy AS
(
SELECT Level=0, Car, Child, Parent, Category_Name
FROM #TestEnvironment
WHERE Car IS NOT NULL
UNION ALL
SELECT Level+1, a.Car, b.Child, b.Parent, b.Category_Name
FROM UnravelHiearchy a
JOIN #TestEnvironment b ON a.Parent = b.Child
)
SELECT Car
,Level1=MAX(CASE WHEN Level = 1 THEN Category_Name END)
,Level2=MAX(CASE WHEN Level = 2 THEN Category_Name END)
,Level3=MAX(CASE WHEN Level = 3 THEN Category_Name END)
,Level4=MAX(CASE WHEN Level = 4 THEN Category_Name END)
,Level5=MAX(CASE WHEN Level = 5 THEN Category_Name END)
,Level6=MAX(CASE WHEN Level = 6 THEN Category_Name END)
FROM UnravelHiearchy
GROUP BY Car
ORDER BY Car;




This way, you'd need to know the maximum number of levels for any car, and add Leveln= up to that level.

The alternative if you don't know the number of levels is that you could insert the unraveled hierarchy into a temp table and then use that along with some dynamic SQL to PIVOT the number of Level columns you need.

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Edit: Sorry to be a bit redundant with what Cadavre posted. No coffee yet this morning so I didn't read it carefully.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ramos.ferdinand
ramos.ferdinand
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
dwain.c it works!!!!!! Wow

cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.

Thanks a lot guys!!! Cheers!
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 6431
Allow me to make amends for my prior transgression with my take on the Dynamic SQL version:


WITH UnravelHiearchy AS
(
SELECT Level=0, Car, Child, Parent, Category_Name
FROM #TestEnvironment
WHERE Car IS NOT NULL
UNION ALL
SELECT Level+1, a.Car, b.Child, b.Parent, b.Category_Name
FROM UnravelHiearchy a
JOIN #TestEnvironment b ON a.Parent = b.Child
)
SELECT Level, Car, Child, Parent, Category_Name
INTO #Temp
FROM UnravelHiearchy;

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = '
SELECT Car' +
(
SELECT ' ,Level' + CAST(n AS VARCHAR) + '=MAX(CASE WHEN Level = ' +
CAST(n AS VARCHAR) + ' THEN Category_name END)'
FROM
(
SELECT TOP
(
(SELECT TOP 1 COUNT(*) FROM #Temp GROUP BY Car ORDER BY 1 DESC) - 1
) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns
) a(n)
ORDER BY n
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
)
+ ' FROM #Temp
GROUP BY Car
ORDER BY Car;'

--PRINT @SQL;

EXEC (@SQL);

GO
DROP TABLE #Temp;




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4287 Visits: 6431
ramos.ferdinand (10/17/2013)
dwain.c it works!!!!!! Wow

cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.

Thanks a lot guys!!! Cheers!


Here's a good article by Wayne Sheffield on using FOR XML PATH to concatenate strings from multiple rows:
Creating a comma-separated list (SQL Spackle)

Cadavre and I are doing basically the same thing, just a slightly different approach.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
ramos.ferdinand (10/17/2013)
dwain.c it works!!!!!! Wow

cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.

Thanks a lot guys!!! Cheers!


Sorry, have been massively busy at work. Dwain's method for solving the issue of having different numbers of categories per car is the same that I'd been thinking of and the article he linked on creating a comma separated list will explain what I was doing with the FOR XML PATH stuff. If you need more help, post back.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search