Home » SQL & PL/SQL » SQL & PL/SQL » Generating group of sets from table (10g)
| Generating group of sets from table [message #640645] |
Sun, 02 August 2015 13:39  |
 |
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
|
Senior Member |
|
|
I have a table containing information about 3 categories of items , bolts , nuts and washers , i want to present it as set or summary report based on rows in the table.
CREATE TABLE BOLTS_DETAIL (PS_STRUCT VARCHAR2(12), PS_CODE VARCHAR2(12) , PS_DESC VARCHAR2(12),PS_DIA NUMBER,SL_LENGTH NUMBER, PS_QTY NUMBER )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'HB12-40' , 'Bolts' ,12 ,40 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'N12' , 'Nuts' ,12 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'W12' , 'Washer' ,12 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'HB20-50' , 'Bolts' ,20 ,50 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'N20' , 'Nuts' ,20 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'LN20' , 'Nuts' ,20 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'W20' , 'Washer' ,20 ,0 , 30 )
SELECT ps_struct, ps_dia, ps_desc, COUNT (ps_desc)
FROM bolts_detail
GROUP BY ps_struct, ps_dia, ps_desc
ORDER BY 2, 3;
PS_STRUCT,PS_DIA,PS_DESC,COUNT(PS_DESC)
1510019 12 Bolts 1
1510019 12 Nuts 1
1510019 12 Washer 1
1510019 20 Bolts 1
1510019 20 Nuts 2
1510019 20 Washer 1
--i want it in the below format
ps_struct ps_dia desc
1510019 12 1bolts_1Nuts_1Washer
1510019 20 1bolts_2Nuts_1Washer
|
|
|
|
| Re: Generating group of sets from table [message #640646 is a reply to message #640645] |
Sun, 02 August 2015 14:33  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> col desc format a50
SQL> with
2 data as (
3 SELECT ps_struct, ps_dia, ps_desc, COUNT (ps_desc) cnt,
4 row_number() over (partition by ps_struct, ps_dia order by ps_desc) rn
5 FROM bolts_detail
6 GROUP BY ps_struct, ps_dia, ps_desc
7 )
8 select ps_struct, ps_dia,
9 substr(sys_connect_by_path(cnt||ps_desc,'_'),2) "DESC"
10 from data
11 where connect_by_isleaf = 1
12 connect by prior ps_struct = ps_struct
13 and prior ps_dia = ps_dia
14 and prior rn = rn - 1
15 start with rn = 1
16 ORDER BY 1, 2;
PS_STRUCT PS_DIA DESC
------------ ---------- --------------------------------------------------
1510019 12 1Bolts_1Nuts_1Washer
1510019 20 1Bolts_2Nuts_1Washer
2 rows selected.
|
|
|
|
| Re: Generating group of sets from table [message #640647 is a reply to message #640646] |
Sun, 02 August 2015 14:38 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
It is easier in 11g where you can use the LISTAGG built-in function:
SQL> with
2 data as (
3 SELECT ps_struct, ps_dia, ps_desc, COUNT (ps_desc) cnt
4 FROM bolts_detail
5 GROUP BY ps_struct, ps_dia, ps_desc
6 )
7 select ps_struct, ps_dia,
8 listagg(cnt||ps_desc,'_') within group (order by ps_desc) "DESC"
9 from data
10 group by ps_struct, ps_dia
11 order by ps_struct, ps_dia
12 /
PS_STRUCT PS_DIA DESC
------------ ---------- --------------------------------------------------
1510019 12 1Bolts_1Nuts_1Washer
1510019 20 1Bolts_2Nuts_1Washer
2 rows selected. |
|
|
|
| Re: Generating group of sets from table [message #640648 is a reply to message #640645] |
Sun, 02 August 2015 14:41 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
with t as (
select ps_struct,
ps_dia,
ps_desc,
count(*) cnt
from bolts_detail
group by ps_struct,
ps_dia,
ps_desc
)
select ps_struct,
ps_dia,
listagg(cnt || ps_desc,'_') within group(order by ps_desc) ps_desc
from t
group by ps_struct,
ps_dia
/
PS_STRUCT PS_DIA PS_DESC
------------ ---------- --------------------
1510019 12 1Bolts_1Nuts_1Washer
1510019 20 1Bolts_2Nuts_1Washer
SQL>
SY.
|
|
|
|
|
|
| Re: Generating group of sets from table [message #640650 is a reply to message #640649] |
Sun, 02 August 2015 15:04 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Yes, I missed OP is on 10G. Then XMLAGG solution:
with t as (
select ps_struct,
ps_dia,
ps_desc,
count(*) cnt
from bolts_detail
group by ps_struct,
ps_dia,
ps_desc
)
select ps_struct,
ps_dia,
rtrim(xmlagg(xmlelement(e,cnt || ps_desc,'_').extract('//text()') order by ps_desc),'_') ps_desc
from t
group by ps_struct,
ps_dia
/
PS_STRUCT PS_DIA PS_DESC
------------ ---------- ---------------------
1510019 12 1Bolts_1Nuts_1Washer
1510019 20 1Bolts_2Nuts_1Washer
SQL>
SY.
[Updated on: Sun, 02 August 2015 15:25] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Generating group of sets from table [message #640690 is a reply to message #640651] |
Mon, 03 August 2015 13:55 |
|
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
|
Senior Member |
|
|
hi michael, things are getting little trickier , with one more requirement, there is also a need of length field consideration in query.I mean there is one more column called length where rows needs to get separated.
CREATE TABLE BOLTS_DETAIL (PS_STRUCT VARCHAR2(12), PS_CODE VARCHAR2(12) , PS_DESC VARCHAR2(12),PS_DIA NUMBER,SL_LENGTH NUMBER, PS_QTY NUMBER )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'HB12-40' , 'Bolts' ,12 ,40 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'N12' , 'Nuts' ,12 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'W12' , 'Washer' ,12 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'HB20-50' , 'Bolts' ,20 ,50 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY ) -- new insert
VALUES ('1510019' , 'HB20-70' , 'Bolts' ,20 ,70 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'N20' , 'Nuts' ,20 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'LN20' , 'Nuts' ,20 ,0 , 30 )
INSERT INTO BOLTS_DETAIL (PS_STRUCT , PS_CODE , PS_DESC ,PS_DIA ,SL_LENGTH , PS_QTY )
VALUES ('1510019' , 'W20' , 'Washer' ,20 ,0 , 30 )
select * from bolts_Detail order by 4,5
PS_STRUCT,PS_CODE,PS_DESC,PS_DIA,SL_LENGTH,PS_QTY
1510019 N12 Nuts 12 0 30
1510019 W12 Washer 12 0 30
1510019 HB12-40 Bolts 12 40 30
1510019 LN20 Nuts 20 0 30
1510019 N20 Nuts 20 0 30
1510019 W20 Washer 20 0 30
1510019 HB20-50 Bolts 20 50 30
1510019,HB20-70 Bolts 20 70 30
--the query i tried
select ps_struct, ps_dia,
substr(sys_connect_by_path(cnt||ps_desc,'_'),2) "DESC"
from (
SELECT ps_struct, ps_dia,sl_length, ps_desc, COUNT (ps_desc) cnt,
row_number() over (partition by ps_struct, ps_dia,sl_length order by ps_desc) rn
FROM bolts_detail
GROUP BY ps_struct, ps_dia, ps_desc,sl_length
)
where connect_by_isleaf = 1
connect by prior ps_struct = ps_struct
and prior ps_dia = ps_dia
and prior rn = rn - 1
start with rn = 1
ORDER BY 1, 2;
--getting the below results.
PS_STRUCT,PS_DIA,DESC
1510019,12,1Bolts_1Washer
1510019,12,1Nuts_1Washer
1510019,20,1Bolts_1Washer
1510019,20,1Bolts_1Washer
1510019,20,2Nuts_1Washer
--what i need is as below since 2 nuts are of same dia they belong to both 50 and 70 length.for example ps_dia 20 has two bolts 50 and 70
and if there are 2 nuts means out of 30 qty ,it will get evenly distributed to these two types of bolts as 15 each, 15 will go for dia 50 and 15 of washers will go to 70,and the same applies to washers but since there is one washer it will take the entire qty.
PS_STRUCT PS_DIA DESC sl_length sum(sl_qty)
1510019 12 1Bolts_1Nuts_1Washer 40 90
1510019 20 1Bolts_2Nuts_1Washer 50 75
1510019 20 1Bolts_2Nuts_1Washer 70 75
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 01:51:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
