Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a Hierarchy Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:08 AM
Points: 6, Visits: 47
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.
Post #1505122
Posted Wednesday, October 16, 2013 4:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 2,443, Visits: 7,556
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1505131
Posted Thursday, October 17, 2013 12:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:08 AM
Points: 6, Visits: 47
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.

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.
Post #1505497
Posted Thursday, October 17, 2013 3:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 2,443, Visits: 7,556
ramos.ferdinand (10/17/2013)
Sorry guys if my message was not that clear and havent put the DDL.

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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1505546
Posted Thursday, October 17, 2013 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:08 AM
Points: 6, Visits: 47
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.

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,''
);
Post #1505726
Posted Thursday, October 17, 2013 6:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
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!
Post #1505955
Posted Thursday, October 17, 2013 7:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:08 AM
Points: 6, Visits: 47
dwain.c it works!!!!!!

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!
Post #1505957
Posted Thursday, October 17, 2013 8:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
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!
Post #1505959
Posted Thursday, October 17, 2013 8:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
ramos.ferdinand (10/17/2013)
dwain.c it works!!!!!!

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!
Post #1505960
Posted Wednesday, October 23, 2013 5:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 2,443, Visits: 7,556
ramos.ferdinand (10/17/2013)
dwain.c it works!!!!!!

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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1507554
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse