I have a Budgets table with two columns:
BudgetID BudgetPath 1 HR 1 HRIT 1 Americas 2 HR 2 Staffing 3 IT 3 Finance 3 HR 3 Asia Pacific
Is it possible in T-SQL or PL/SQL to string together the values in BudgetPath, grouped by BudgetID, so the output would be:
BudgetID BudgetPath 1 HR>HRIT>Americas 2 HR>Staffing 3 IT>Finance>HR>Asia Pacific
Sadly, there is nothing quite as elegant for T-SQL or PL/SQL as MySQL's wonderful GROUP_CONCAT function. The drop-dead simple solution using GROUP_CONCAT is:
select BudgetID , group_concat(BudgetPath separator '>') as BudgetPaths from Budgets group by BudgetID
For Oracle, have a look at: Displaying multiple records in one row.
For T-SQL, here's a method that will work. First, create a temporary table like this:
create table TempBudgetPaths ( BudgetID integer not null primary key , BudgetPaths varchar(max) null )
Next, populate this table with keys:
insert into TempBudgetPaths ( BudgetID ) select distinct BudgetID from Budgets
Finally, update the temporary table like this:
DECLARE @row INTEGER, @maxrows INTEGER SELECT @row=1, @maxrows=count(*) FROM Budgets WHILE @row <= @maxrows BEGIN UPDATE TempBudgetPaths SET BudgetPaths = COALESCE(t1.BudgetPaths+'>','') + t2.BudgetPath FROM TempBudgetPaths t1 INNER JOIN ( SELECT BudgetID , BudgetPath , ROW_NUMBER() OVER ( ORDER BY BudgetID , BudgetPath ) as rownum FROM Budgets ) AS t2 ON t2.BudgetID = t1.BudgetID WHERE t2.rownum = @row SELECT @row = @row + 1 END SELECT * FROM TempBudgetPaths
Not nearly as pretty as GROUP_CONCAT, is it. Nevertheless, it works, and produces these results:
BudgetID BudgetPaths 1 Americas>HR>HRIT 2 HR>Staffing 3 Asia Pacific>Finance>HR>IT
Notice the sequence in which the BudgetPaths were appended. This is a result of the OVER (ORDER BY ...) clause. This is not the same as your sequence, which appears to be some kind of hierarchy. To achieve your sequence will require an additional data column, to indicate sequence of BudgetPath within BudgetID.
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.