December 9, 2015 at 3:33 am
Hi
I have this table
CREATE TABLE [dbo].[GroupS](
[Id] [int] NULL,
[Title] [nvarchar](255) NULL,
[ParentId] [int] NULL,
[ParentTitle] [nvarchar](255) NULL,
[ParentLevel] [tinyint] NULL
) ON [PRIMARY]
GO
Insert into Groups(id,title,parentid,parentTitle,ParentLevel)
Values
(1,'Group1',Null,Null,0),
(2,'Group2',Null,Null,0),
(3,'Group3',Null,Null,0),
(4,'Group4',Null,Null,0),
(5,'Group5',4,'Group4',1),
(10,'TenthGroup',1,'Group1',1),
(10,'TenthGroup',2,'Group2',2),
(10,'TenthGroup',3,'Group3',3),
(9 ,'NinetheGroup',4,'Group4',1),
(9 ,'NinetheGroup',5,'Group5',2)
I want a pivot View with this output :
Id ---- Title ----- ParentIdLevel1 ---- ParentTitleLevel1 ----- ParentIdLevel2 ---- ParentTitleLevel2 ----- ParentIdLevel3 ---- ParentTitleLevel3
1 Group1 Null Null Null Null Null Null
2 Group2 Null Null Null Null Null Null
3 Group3 Null Null Null Null Null Null
4 Group4 Null Null Null Null Null Null
5 Group5 4 Group4 Null Null Null Null
9 NinetheGroup 1 Group1 2 Group2 3 Group3
10 TenthGroup 4 Group4 5 Group5 Null Null
The Problem is :
I have 2 values ([ParentId] , [ParentTitle) that should be under 2 Pivot Columns (ParentIdLevelX ---- ParentTitleLevelX).
December 9, 2015 at 4:51 am
Pivot is just a convenience method to say
select id,title,
ParentIdLevel1 = max(case ParentLevel when 1 then ParentId end),
ParentTitleLevel1 = max(case ParentLevel when 1 then ParentTitle end),
ParentIdLevel2 = max(case ParentLevel when 2 then ParentId end),
ParentTitleLevel2 = max(case ParentLevel when 2 then ParentTitle end),
ParentIdLevel3 = max(case ParentLevel when 3 then ParentId end),
ParentTitleLevel3 = max(case ParentLevel when 3 then ParentTitle end)
from Groups
group by id,title
December 9, 2015 at 5:15 am
:-D:-D
Thank you
I was busy with Pivot from several hours. :Whistling:
December 14, 2015 at 2:27 am
serg-52 (12/9/2015)
Pivot is just a convenience method to say
select id,title,
ParentIdLevel1 = max(case ParentLevel when 1 then ParentId end),
ParentTitleLevel1 = max(case ParentLevel when 1 then ParentTitle end),
ParentIdLevel2 = max(case ParentLevel when 2 then ParentId end),
ParentTitleLevel2 = max(case ParentLevel when 2 then ParentTitle end),
ParentIdLevel3 = max(case ParentLevel when 3 then ParentId end),
ParentTitleLevel3 = max(case ParentLevel when 3 then ParentTitle end)
from Groups
group by id,title
I have seen pivot and the Group By create different plans. In one specific case I got the pivot version to run in the third of the time so it is not straight replica of each other. I did this because a sqldev colleague of mine made the blanket statement that pivoting is always worse than case statement.
December 14, 2015 at 2:47 am
MadAdmin (12/14/2015)
serg-52 (12/9/2015)
Pivot is just a convenience method to say
select id,title,
ParentIdLevel1 = max(case ParentLevel when 1 then ParentId end),
ParentTitleLevel1 = max(case ParentLevel when 1 then ParentTitle end),
ParentIdLevel2 = max(case ParentLevel when 2 then ParentId end),
ParentTitleLevel2 = max(case ParentLevel when 2 then ParentTitle end),
ParentIdLevel3 = max(case ParentLevel when 3 then ParentId end),
ParentTitleLevel3 = max(case ParentLevel when 3 then ParentTitle end)
from Groups
group by id,title
I have seen pivot and the Group By create different plans. In one specific case I got the pivot version to run in the third of the time so it is not straight replica of each other. I did this because a sqldev colleague of mine made the blanket statement that pivoting is always worse than case statement.
"the Group By" is generally known as a Cross Tab [/url]query and generally they will perform a little better than the pivot operator.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 14, 2015 at 4:31 am
ChrisM@Work (12/14/2015)
MadAdmin (12/14/2015)
serg-52 (12/9/2015)
Pivot is just a convenience method to sayI have seen pivot and the Group By create different plans. In one specific case I got the pivot version to run in the third of the time so it is not straight replica of each other. I did this because a sqldev colleague of mine made the blanket statement that pivoting is always worse than case statement.
"the Group By" is generally known as a Cross Tab [/url]query and generally they will perform a little better than the pivot operator.
I agree that a cross tab may perform a little better in certain cases, if you are doing case statements that dont involve returning columns, i.e.
max((case ParentLevel when 2 then 1 else 0 end))
I have played with a couple of cases where the cross tab was not able to get parallelism going despite being run against large tables with fresh index rebuilds and stats.
When I got rid of the column in both the cross tab and the pivot,i.e.
CASE WHEN BlahBlah THEN ParentId END and replaced it with below, I got parallelism in both techniques.
CASE WHEN BlahBlah THEN 1 else 0 ENDWhen I put the columns back into the cross tab and the pivot as per below, I immediately lost parallelism in that specific case for the cross tab only but not the pivot.
max((case ParentLevel when 2 then ParentTitle else 0 end))
So not saying pivot is better, worse or equal, as these claims that are falsifiable and have already been proven to be false.
Just saying that CrossTab may be convenient, but not equal or better than pivot for all queries.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply