1

I have a table of category and has an attribute of

This tag_id is the sequence or pattern on what its parent or child is.

Sample:

CREATE TABLE category (
    id int,
    parent_id int, 
    code varchar(255),
    name varchar(255).
    tag_id varchar(255)
);

INSERT INTO category (id, parent_id, code, name, tag_id)
VALUES
    (1,null,0,'category 1','|1|'),
    (2,null,1,'category 2','|2|'),
    (3,null,2,'category 3','|3|'),
    (4,null,3,'category 4','|4|'),
    (5,4,4,'category 5','|4|5|'),
    (6,5,5,'category 6','|4|5|6|');

As you can see, you may determine if the category is parent, if the first value inside the tag_id is its id.

I want to group by this category by same first parent id

Reason: I'm joining this table to another table wherein there's an amount column and I want to sum and group by using the first parent id.

For example I have a sales table wherein it has a relationship with product table. And this product table has relationship with category table.

In sales table it has column there total_amount, and product_id. This product_id is linked to the id of the product table. And this product table has a category_id column that is linked to the id of the category table.

The main goal is to get the SUM OF TOTAL_AMOUNT that a first level category was sold. I just need how to split this | character and get only the first level category

Thank you!

Star
  • 161
  • 6
  • 15
  • 2
    Where is the comma in your sample data? What is your expected output? Whether other table also involved if yes then provide the DDL and sample data for that table also. – Suraj Kumar Feb 14 '20 at 06:47
  • @SurajKumar I'm sorry, I edited the title – Star Feb 14 '20 at 06:51
  • 2
    @Star What's the SQL Server version? – gotqn Feb 14 '20 at 06:52
  • 3
    Please replace your structure and data with CREATE TABLE and INSERT INTO scripts, and show desired result for shown data. – Akina Feb 14 '20 at 06:53
  • It's hard to tell, but these links might get you going in the right direction; https://stackoverflow.com/questions/19914472/cte-to-get-all-children-descendants-of-a-parent or https://dba.stackexchange.com/questions/138928/recursive-cte-to-find-total-for-all-children – LJ01 Feb 14 '20 at 07:04
  • 1
    Well. now we'd like to see desired result for this data. *The main goal is to get the SUM OF TOTAL_AMOUNT that a first level category was sold.* There is NO the field named `TOTAL_AMOUNT` in source data - nothing to summarize. – Akina Feb 14 '20 at 07:13
  • Why I can't use STRING_SPLIT. I just try and got an error message of: Msg 208, Level 16, State 1, Line 1 Invalid object name 'STRING_SPLIT'. – Star Feb 14 '20 at 07:29
  • 1
    @Star **gotqn** asks you "What's the SQL Server version?" 40 minutes ago. You ignore this question - so I decided that you have the most recent version. Can you at least now specify the version of your server? simply execure `select @@version;` and post the result. – Akina Feb 14 '20 at 07:35
  • I'm not sure but I just go to sql configuration server manager, and I just found out that the file version is 2017.140.1000.169 – Star Feb 14 '20 at 07:43
  • I run this command also select @@version; Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 (Build 17763: ) (Hypervisor) – Star Feb 14 '20 at 07:43
  • What is the correct value for row with `id` equal to `6` - `'|4|5|6'` or `'|4|5|6|'`? – Zhorov Feb 14 '20 at 07:46
  • @Zhorov the '|4|5|6|' – Star Feb 14 '20 at 07:49
  • You can improve your design by spliting the data into two tables; having multiple identifiers in one field is a bad idea.Put the tags into a separate table with one tag per record. – Peter Smith Feb 14 '20 at 08:01

1 Answers1

1

If you need the first distinct values of tag no need to group also

    Select distinct id as tag_id from (SELECT SUBSTRING(s.tag_id,3,CHARINDEX('|',S.tag_id,3)-3) id from category s) temp
Deepak Kumar
  • 298
  • 1
  • 7
  • I cannot substring because. the length of the id is not constant to 3 characters. – Star Feb 14 '20 at 07:14
  • @star I have updated query where it will consider length of first id. Only thing to consider is all records starts with [|, so we are substring from 3rd char – Deepak Kumar Feb 14 '20 at 08:15
  • Your insert statement data differs from actual image shown earlier. Please find updated query https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=4e13ac24a381efa6dec652c2f717baaf – Deepak Kumar Feb 14 '20 at 08:25
  • Thanks sir. This solution works for me. I update your solution into like this: SELECT SUBSTRING(s.tag_id,2,(CHARINDEX('|',S.tag_id,2))-2) id from category s And get the expected result – Star Feb 14 '20 at 08:32