Help on Hierarchy Query

Question

Post reply

Help on Hierarchy Query

thomashohner

SSCrazy Eights

Points: 9856
More actions
November 2, 2016 at 10:25 am

#400980

Hello All,
I have a dev friend who asked me to look at this data set and present it into a Hierarchy format. I am having trouble finding a good way to do it. Started out with windows functions but that does not seem to be working well.
Here is the query if you look at the Levels column that is the basis for the Hierarchy it needs to be Meeting, Subgroup, Session, Attendee.
Any help is appreciated Thanks !!
--Meeting, Subgroup, Session
CREATE TABLE #Table (MeetingID INT , Levels VARCHAR(20), ProviderName VARCHAR(20), PatientName VARCHAR(20), SubGroupName VARCHAR(20),SessionName VARCHAR(20))
INSERT INTO #Table VALUES
(369, 'Meeting', 'Bixby, Heather',NULL, NULL, NULL)
,(369, 'Meeting', 'Crews, Julie',NULL, NULL, NULL)
,(369, 'Meeting', 'Parker, John',NULL, NULL, NULL)
,(369, 'Session', 'Crews, Julie',NULL, 'AA Group', 'AA Group')
,(369, 'Session', 'Parker, John',NULL, 'AA Group', 'AA Group')
,(369, 'SubGroup', 'Crews, Julie',NULL, 'AA Group', 'AA Group')
,(369, 'SubGroup', 'Parker, John',NULL, 'AA Group', 'AA Group')
,(369, 'Attendee', 'Crews, Julie','Caldwell,Walter', 'AA Group', 'AA Group')
,(369, 'Attendee', 'Parker, John','Caldwell,Walter','AA Group', 'AA Group')
,(369, 'Attendee', 'Parker, John','Callaway,Carl', 'AA Group', 'AA Group')
,(369, 'Attendee', 'Crews, Julie','Callaway,Carl', 'AA Group', 'AA Group')
,(369, 'SubGroup', 'Bixby, Heather',NULL,'AA Group', 'AB Group')
,(369, 'Session', 'Bixby, Heather',NULL, 'AA Group', 'AB Group')
,(369, 'Attendee', 'Bixby, Heather','Craig, Jenny', 'AA Group', 'AB Group')
SELECT *
, DENSE_RANK() OVER(PARTITION BY MEETINGID ORDER BY (SELECT NULL)) AS SubGroupOrder
, DENSE_RANK() OVER(PARTITION BY SubgroupName ORDER BY SessionName) AS SessionOrder
, DENSE_RANK() OVER(PARTITION BY SessionName ORDER BY PatientName) AS AttendeeOrder
FROM #Table;
DROP TABLE #Table
***SQL born on date Spring 2013:-)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 1

I think i figured it out. Ugly SQL but it seems to work.

Thanks

--Meeting, Subgroup, Session

CREATE TABLE #Table (MeetingID INT , Levels VARCHAR(20), ProviderName VARCHAR(20), PatientName VARCHAR(20), SubGroupName VARCHAR(20),SessionName VARCHAR(20))

INSERT INTO #Table VALUES

(369, 'Meeting', 'Bixby, Heather',NULL, NULL, NULL)

,(369, 'Meeting', 'Crews, Julie',NULL, NULL, NULL)

,(369, 'Meeting', 'Parker, John',NULL, NULL, NULL)

,(369, 'Session', 'Crews, Julie',NULL, 'AA Group', 'AA Group')

,(369, 'Session', 'Parker, John',NULL, 'AA Group', 'AA Group')

,(369, 'SubGroup', 'Crews, Julie',NULL, 'AA Group', 'AA Group')

,(369, 'SubGroup', 'Parker, John',NULL, 'AA Group', 'AA Group')

,(369, 'Attendee', 'Crews, Julie','Caldwell,Walter', 'AA Group', 'AA Group')

,(369, 'Attendee', 'Parker, John','Caldwell,Walter','AA Group', 'AA Group')

,(369, 'Attendee', 'Parker, John','Callaway,Carl', 'AA Group', 'AA Group')

,(369, 'Attendee', 'Crews, Julie','Callaway,Carl', 'AA Group', 'AA Group')

,(369, 'SubGroup', 'Bixby, Heather',NULL,'AA Group', 'AB Group')

,(369, 'Session', 'Bixby, Heather',NULL, 'AA Group', 'AB Group')

,(369, 'Attendee', 'Bixby, Heather','Craig, Jenny', 'AA Group', 'AB Group')

SELECT

piv.MeetingID

, piv.ProviderName

, piv.SubGroupName

, Piv.SessionName

, piv.Attendee

,DENSE_RANK() OVER(PARTITION BY MeetingID ORDER BY SubgroupName) AS SubGroupOrder

,DENSE_RANK() OVER(PARTITION BY SubgroupName ORDER BY SessionName) AS SessionOrder

,DENSE_RANK() OVER(PARTITION BY SessionName ORDER BY piv.Attendee) AS AttendeeOrder

FROM

(

SELECT *

,DENSE_RANK() OVER(PARTITION BY SessionName ORDER BY PatientName) AS KeepsMaxFromOne

FROM #Table

) src

PIVOT

(

MAX([PatientName]) FOR Levels IN (Meeting, Attendee, Session, SubGroup)

) AS Piv

WHERE piv.Attendee IS NOT NULL

DROP TABLE #Table

***SQL born on date Spring 2013:-)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

thomashohner (11/2/2016)
I think i figured it out. Ugly SQL but it seems to work.
Thanks
--Meeting, Subgroup, Session
CREATE TABLE #Table (MeetingID INT , Levels VARCHAR(20), ProviderName VARCHAR(20), PatientName VARCHAR(20), SubGroupName VARCHAR(20),SessionName VARCHAR(20))
INSERT INTO #Table VALUES
(369, 'Meeting', 'Bixby, Heather',NULL, NULL, NULL)
,(369, 'Meeting', 'Crews, Julie',NULL, NULL, NULL)
,(369, 'Meeting', 'Parker, John',NULL, NULL, NULL)
,(369, 'Session', 'Crews, Julie',NULL, 'AA Group', 'AA Group')
,(369, 'Session', 'Parker, John',NULL, 'AA Group', 'AA Group')
,(369, 'SubGroup', 'Crews, Julie',NULL, 'AA Group', 'AA Group')
,(369, 'SubGroup', 'Parker, John',NULL, 'AA Group', 'AA Group')
,(369, 'Attendee', 'Crews, Julie','Caldwell,Walter', 'AA Group', 'AA Group')
,(369, 'Attendee', 'Parker, John','Caldwell,Walter','AA Group', 'AA Group')
,(369, 'Attendee', 'Parker, John','Callaway,Carl', 'AA Group', 'AA Group')
,(369, 'Attendee', 'Crews, Julie','Callaway,Carl', 'AA Group', 'AA Group')
,(369, 'SubGroup', 'Bixby, Heather',NULL,'AA Group', 'AB Group')
,(369, 'Session', 'Bixby, Heather',NULL, 'AA Group', 'AB Group')
,(369, 'Attendee', 'Bixby, Heather','Craig, Jenny', 'AA Group', 'AB Group')
SELECT
piv.MeetingID
, piv.ProviderName
, piv.SubGroupName
, Piv.SessionName
, piv.Attendee
,DENSE_RANK() OVER(PARTITION BY MeetingID ORDER BY SubgroupName) AS SubGroupOrder
,DENSE_RANK() OVER(PARTITION BY SubgroupName ORDER BY SessionName) AS SessionOrder
,DENSE_RANK() OVER(PARTITION BY SessionName ORDER BY piv.Attendee) AS AttendeeOrder
FROM
(
SELECT *
,DENSE_RANK() OVER(PARTITION BY SessionName ORDER BY PatientName) AS KeepsMaxFromOne
FROM #Table
) src
PIVOT
(
MAX([PatientName]) FOR Levels IN (Meeting, Attendee, Session, SubGroup)
) AS Piv
WHERE piv.Attendee IS NOT NULL
DROP TABLE #Table

You may find this a little prettier...

SELECT piv.*,

DENSE_RANK() OVER (PARTITION BY MeetingID ORDER BY SubgroupName) AS SubGroupOrder,

DENSE_RANK() OVER (PARTITION BY SubgroupName ORDER BY SessionName) AS SessionOrder,

DENSE_RANK() OVER (PARTITION BY SessionName ORDER BY Attendee) AS AttendeeOrder

FROM

(

SELECT

meetingID,

ProviderName,

SubGroupName,

SessionName,

Attendee = PatientName

FROM #Table

WHERE levels = 'Attendee'

) piv;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 3

Thanks Alan !

One of these days i'll get good like that. Yes, that was much simplified. Looks like I was over thinking it.

😀

***SQL born on date Spring 2013:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Why do you even need a derived table?

SELECT

meetingID,

ProviderName,

SubGroupName,

SessionName,

Attendee = PatientName,

DENSE_RANK() OVER (PARTITION BY MeetingID ORDER BY SubgroupName) AS SubGroupOrder,

DENSE_RANK() OVER (PARTITION BY SubgroupName ORDER BY SessionName) AS SessionOrder,

DENSE_RANK() OVER (PARTITION BY SessionName ORDER BY PatientName) AS AttendeeOrder

FROM #Table

WHERE levels = 'Attendee'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Luis Cazares (11/2/2016)
Why do you even need a derived table?
SELECT
meetingID,
ProviderName,
SubGroupName,
SessionName,
Attendee = PatientName,
DENSE_RANK() OVER (PARTITION BY MeetingID ORDER BY SubgroupName) AS SubGroupOrder,
DENSE_RANK() OVER (PARTITION BY SubgroupName ORDER BY SessionName) AS SessionOrder,
DENSE_RANK() OVER (PARTITION BY SessionName ORDER BY PatientName) AS AttendeeOrder
FROM #Table
WHERE levels = 'Attendee'

Duh. <smacking own forehead>

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001