Grouping Columns or Making rows into columns

Question

Hello,
I am trying to build the query based on following report data I would like to publish.

TREELEVEL   TempID  TempPID Label   Name  
0   33430   34774   Price Folder    Price Folder  
1   33433   33430   Program 1.0 Program  
1   33435   33430   Records 2.0 Records  
1   33437   33430   ForwardPrices   3.0 dForwardPrices  
1   33439   33430   CashPrices  4.0 CashPrices  
1   34368   33430   Rates   5.0 Rates  
1   34490   33430   Email   6.0 Email   

If you see above table data, TreeLevel = 0 is Parent records and underneath all the different sub folder so TempID = 33430 is parent TempID and underneath all the TempPID is same 33430, I need all the records including parent.

Query

 SELECT   TREELEVEL, TempID, TempPID  ,  Label, Name FROM Instances  
 WHERE [TempPID] =  33430  
GROUP BY  TREELEVEL, TempID, TempPID  ,  Label, Name  
ORDER BY Label  

Blockquote

]2

So my result will like:

Note: I need for each TempPID and corresponding TempID in group, There are multiple Labels (Folders) so each in own separate group

Thanks

Answer 1

Lihong, Thanks.

I have all following columns but I need all the result in each associated group.
TREELEVEL, TempID, TempPID , Label, Name

If you see in result, TempID is ParentId (example 33430) and it has associated TempPID (example 33430), I am looking to retrieve all the associated results with Parent (TempID ) and Child (TempPID)

TenpID 33430 has Parent Folder is Price Folder (define as Label column) and underneath it has all sub folders like: Program , Records, ForwardPrices, CashPrices , Rates, Email (Which is defined under Name column) so it's looks like:

Program
Program
Records
ForwardPrices
CashPrices
Rates
Email

So it's kind of Folder and it has multiple files kind of apprach which I am trying to get dynamically for each main Folder (Label)

Answer 2

Please see following script, Thanks for help!

CREATE TABLE [dbo].[JobList](
[TempPID] [int] NOT NULL,
[TempID] [int] NOT NULL,
[TreeLevel] [int] NOT NULL,
[Label] nvarchar NOT NULL,
[Name] nvarchar NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (34774, 33430, 0, N'Price Folder', N'Price Folder')
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (33430, 33433, 1, N'Program', N'1.0 Program')
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (33430, 33435, 1, N'Records', N'2.0 Records')
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (33430, 33437, 1, N'ForwardPrices', N'3.0 ForwardPrices')
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (33430, 33439, 1, N'CashPrices', N'4.0 CashPrices')
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (33430, 34368, 1, N'Rates', N'5.0 Rates')
GO
INSERT [dbo].[JobList] ([TempPID], [TempID], [TreeLevel], [Label], [Name]) VALUES (33430, 34490, 1, N'Email', N'6.0 Email')
GO

Answer 3

Hi LiHong,
Actually this is not a exact Source table but I have just combined it to reflect the result to make it easy to build the query.

I have folder structure hierarchy like this in one of our tool:

TenpID 33430 has Parent Folder is Price Folder (define as Label column) and underneath it has all sub folders like: Program , Records, ForwardPrices, CashPrices , Rates, Email (Which is defined under Name column) so it's looks like:

Ex.
Price Fodler -- this is a main folder (stored as Label column) and following all stored under Name column
==> Program
==> Records
==> ForwardPrices
==> CashPrices
==> Rates
==> Email

Answer 4

Hi @pdsqsql
Maybe you need recursive CTE as I guessed from your description.
Check this:

DECLARE @root AS INT =33430;  
WITH SubsCTE AS  
	(select TempID,TempPID,Label ,0 as Treelevel  
	 from [JobList]   
	 where TempID= @root  
	 UNION ALL  
	 select C.TempID,C.TempPID,C.Label,P.Treelevel + 1  
	 from SubsCTE P JOIN [JobList] C  ON C.TempPID = P.TempID  
	)  
SELECT TempID,TempPID,Treelevel,Label,   
       CASE WHEN Treelevel=0 THEN Label   
	        ELSE CAST((CAST(ROW_NUMBER()OVER(ORDER BY Treelevel,TempPID)AS decimal(5,1))-1)AS varchar)+Label END AS Name  
FROM SubsCTE  

For more details about Recursive CTE, please refer to this link: SQL Server Recursive CTE

Best regards,
LiHong

Answer 5

Thanks Hong for the query.
Somehow it's throwing any error:

Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting bigint to data type numeric.

I tried to change it to (6,1) from (5,1) and it's working but pulling wrong result as it's pulling 65 records for each PID, it should pull actual records if it's 5 name for each label then only 5 names or if it's 7 name for each label then only 7 names.

ELSE CAST((CAST(ROW_NUMBER()OVER(ORDER BY Treelevel,TemplatePID)AS decimal(6,1))-1) AS varchar)+Label END AS Name