Column into single row comma delimited. 1

[areotree]

I am currently using the query below to pull data from two tables and multiple fields based on a primary key from the first table and two foreign keys in the second and output it to a single row. This working great but the output is spaced out based on column size. I have tried to comma delimit it using "||';'||" in the case statement but I keep getting a group by error. Can someone please help me get this into a comma delimited format.

Thanks.


select
distinct ft.serial_number, ft.state, ft.fap_version
,sum(case when rf_params_.id = ft.params_1x_fk then channel end) ox_channel
,sum(case when rf_params_.id = ft.params_1x_fk then pn_offset end) ox_pn_offset
,sum(case when rf_params_.id = ft.params_1x_fk then tx_power_dbm end) ox_power_dbm
,sum(case when rf_params_.id = ft.params_1x_fk then freq end) ox_freq
,sum(case when rf_params_.id = ft.params_do_fk then channel end) do_channel
,sum(case when rf_params_.id = ft.params_do_fk then pn_offset end) do_pn_offset
,sum(case when rf_params_.id = ft.params_do_fk then tx_power_dbm end) do_power_dbm
,sum(case when rf_params_.id = ft.params_do_fk then freq end) do_freq
from anpdb.femto ft
left join anpdb.rf_params rf_params_ on (ft.params_1x_fk = rf_params_.id) or (ft.params_do_fk = rf_params_.id)
left join hdm_dbuser.device hdev on hdev.serialnumber = ft.serial_number
where regexp_like(ft.state,'[2-9]') or ft.state = '10' and hdev.devicetype_id = '2' and hdev.deleted = '0'
group by
ft.serial_number, ft.state, ft.fap_version
order by fap_version;

 

[SantaMufasa]

Aerotree,

Absent a set of data upon which I can test a solution for you, I'll have to hazard a guess, then have you try it out, then post the results here until we arrive at an acceptable solution for you.

First, your GROUP BY clause should accomplish the "DISTINCT"-ing for you, so let's lose the DISTINCT modifier.

Next, re-try the replacement of your expression-separating commas with ||','||, while also losing your expression aliases.

Please post results, errors, et cetera here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[areotree]

You are the greatest !!! So it was the aliases that was killing me. Why can't you use aliases with the concat ?

Thanks.

output:

select
ft.serial_number||','||ft.state||','||ft.fap_version
||','||sum(case when rf_params_.id = ft.params_1x_fk then channel end)
||','||sum(case when rf_params_.id = ft.params_1x_fk then pn_offset end)
||','||sum(case when rf_params_.id = ft.params_1x_fk then tx_power_dbm end)
||','||sum(case when rf_params_.id = ft.params_1x_fk then freq end)
||','||sum(case when rf_params_.id = ft.params_do_fk then channel end)
||','||sum(case when rf_params_.id = ft.params_do_fk then pn_offset end)
||','||sum(case when rf_params_.id = ft.params_do_fk then tx_power_dbm end)
||','||sum(case when rf_params_.id = ft.params_do_fk then freq end)
from anpdb.femto ft
left join anpdb.rf_params rf_params_ on (ft.params_1x_fk = rf_params_.id) or (ft.params_do_fk = rf_params_.id)
left join hdm_dbuser.device hdev on hdev.serialnumber = ft.serial_number
where regexp_like(ft.state,'[2-9]') or ft.state = '10' and hdev.devicetype_id = '2' and hdev.deleted = '0'
group by
ft.serial_number, ft.state, ft.fap_version
order by fap_version;

0005B9007770,8,2.5.2.65,-3988,-3988,3999999996,-3988,-3988,-3988,3999999996,-3988
0005B90095D6,7,2.5.2.67,5950,3060,170,33107.5,13192,3060,170,33469.6
0005B90095CA,4,2.5.2.84,1875,600,-75,9743.75,3880,600,-100,9844
0005B900772E,7,2.5.2.84,2500,480,0,7845,2700,480,40,7855
0005B900774C,7,2.5.2.84,1050,180,-45,5842.5,2328,540,-60,5906.4
0005B9007752,7,2.5.2.84,1925,168,-105,13606.25,2100,168,-105,13615
0005B9007755,7,2.5.2.84,700,240,-30,3895,1552,360,-40,3937.6
0005B9007773,7,2.5.2.84,2375,300,-75,9768.75,3375,900,-75,9818.75
0005B9007A33,7,2.5.2.84,3000,960,-120,15590,6208,480,-160,15750.4
0005B9007A51,7,2.5.2.84,1750,900,50,9737.5,3880,300,50,9844
0005B9007A87,7,2.5.2.84,8750,3000,-375,48687.5,19400,3000,-450,49220
0005B9007AA8,7,2.5.2.84,3500,600,-150,19475,7760,600,-150,19688
0005B9007B32,7,2.5.2.84,1050,540,30,5842.5,2028,540,30,5891.4
0005B9007B35,7,2.5.2.84,750,240,-30,3897.5,1552,240,-30,3937.6
0005B90095D9,7,2.5.2.84,1500,240,-60,7795,3104,240,-60,7875.2
0005B9007BD4,8,2.5.2.84,1350,504,-45,5857.5,2025,1008,-45,5891.25
0005B900775E,7,2.5.21.601,1875,900,-75,9743.75,3880,300,-75,9844
0005B90079D0,7,2.5.21.601,1400,480,-80,7790,3104,480,-80,7875.2
0005B90079E2,7,2.5.21.601,1400,240,-60,7790,3104,240,-60,7875.2
0005B90095CD,7,2.5.21.601,1750,900,-75,9737.5,3880,600,-100,9844

 

[SantaMufasa]

So it was the aliases that was killing me. Why can't you use aliases with the concat ?

You can create aliases in Oracle for SELECTed expressions and for TABLES (in your FROM clause). By using the concatenation operator ("||") you have "pasted" together, into a single expression, all of your (otherwise-separate) SELECTed expressions. Therefore, you have the syntactical opportunity for a single alias on your single resulting expression just in front of your FROM.


Does that make sense?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[areotree]

Yes, so if I understnd it by using the concat we are are lumping all the columns data into one string so if I wanted to use a alias I would just use one alias for the whole concated info.

 

[Turkbear]

Hi aerotree,
It is the custom here at Tek-Tips,that a star is given if you get an especially helpful response to your posted issue.
In this case, it seems that you would want to give Santa a star since his was a very helpful and concise solution/explanation of your issue.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[areotree]

SantaMufasa, Thank you for the great support.