SQL Query Assistance

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Visitor

SQL Query Assistance

HI All -

 

I have a set of data like below...

 

CategoryIDTier
Mobile1022Tier 3
Mobile1056Tier 2
Checks1077Tier 1
Mobile1077Tier 1
Mobile1077Tier 2
Mobile1077Tier 3
Mobile1093Tier 2
Online1093Tier 1
Online1093Tier 3
Online1096Tier 3
Checks1765Tier 1

 

I need to create a column that shows all tiers for a specific id and corresponding category.

Would like the table to look like this.  If additional columns are needed that is fine but the most important data is below.

CategoryIDTierTIER_1
Mobile1022Tier 3Tier 3
Mobile1056Tier 2Tier 2
Checks1077Tier 1Tier 1
Mobile1077Tier 3Tier 1, Tier 2, Tier 3
Mobile1093Tier 2Tier 2
Online1093Tier 3Tier 1, Tier 3
Online1096Tier 3Tier 3
Checks1765Tier 1Tier 1

 

Any help with this would be greatly appreciated!! Thank you!!

Tags (2)
1 REPLY
Senior Apprentice

Re: SQL Query Assistance

Hi,

 

How about the following?

 

Firstly I've setup a table with your data:

CREATE SET VOLATILE TABLE vt1
(category VARCHAR(20)
, id INTEGER
,tier_level VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt1 VALUES('Mobile',1022,'Tier 3');
INSERT INTO vt1 VALUES('Mobile',1056,'Tier 2');
INSERT INTO vt1 VALUES('Checks',1077,'Tier 1');
INSERT INTO vt1 VALUES('Mobile',1077,'Tier 1');
INSERT INTO vt1 VALUES('Mobile',1077,'Tier 2');
INSERT INTO vt1 VALUES('Mobile',1077,'Tier 3');
INSERT INTO vt1 VALUES('Mobile',1093,'Tier 2');
INSERT INTO vt1 VALUES('Online',1093,'Tier 1');
INSERT INTO vt1 VALUES('Online',1093,'Tier 3');
INSERT INTO vt1 VALUES('Online',1096,'Tier 3');
INSERT INTO vt1 VALUES('Checks',1765,'Tier 1');

SELECT *
FROM vt1
ORDER BY 1,2;

Use recursive processing to build up the string of 'tier' values.

WITH RECURSIVE cat_id_tiers
(category, id, tier_level,all_tiers,depth)
AS
(
SELECT dt1.*
             ,starttier
             ,0
FROM (SELECT category,id,MIN(tier_level) AS starttier
               FROM vt1
			   GROUP BY 1,2) AS dt1
UNION ALL
SELECT tnext.category
   ,tnext.id
   ,tnext.tier_level
   ,tprev.all_tiers||' '||tnext.tier_level
   ,tprev.depth+1
FROM vt1 AS tnext
INNER JOIN cat_id_tiers AS tprev
 ON tprev.category = tnext.category
 AND tprev.id = tnext.id
 AND tprev.tier_level < tnext.tier_level
 AND tprev.depth <= 20
 )
 SELECT *
 FROM cat_id_tiers
QUALIFY depth = MAX(depth) OVER(PARTITION BY category,id)
 ORDER BY 1,2,3;

Does that work for you?

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com