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 NULLBEGIN;
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.