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: 61136 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

thomashohner

SSCrazy Eights

Points: 9856

November 2, 2016 at 1:01 pm

#1910212

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: 183686 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: 61136 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