Question

sql query to arrange template/metadata column wise

Ideas Laserfiche How To Search and Retrieval

Updated September 7, 2024

asked on September 2, 2024

Hello Team,

I want query which will convert metadata from rows structure to column structure.

Actually we have template with multi-level group fields ( see image Group wise query.png) and we need everything in column along with single column like ( table format.png).

We are using below query but it won't be work and giving wrong output.; as for multi-level group some of the values were null but still it dispalyed.

I am using below query.

select distinct * from (
select toc.tocid, LRO_Attorney.pos,LRO_Attorney.pos2, toc.name AS ReportName, LRO_Attorney.str_val as LRO_Attorney, Rulemaking_Stage.str_val as [Rulemaking stage], RIN.str_val as [RIN],Bureau.str_val as [Bureau],Stage_Status.str_val as [Stage_Status],isnull(Phase_of_Review.str_val,'') as [Phase_of_Review]

from toc inner join propval as LRO_Attorney on toc.tocid = LRO_Attorney.tocid and LRO_Attorney.prop_id = 1515
inner join propval as Rulemaking_Stage on toc.tocid = Rulemaking_Stage.tocid and Rulemaking_Stage.prop_id = 1269
inner join propval as RIN on toc.tocid = RIN.tocid and RIN.prop_id = 1267
inner join propval as Bureau on toc.tocid = Bureau.tocid and Bureau.prop_id = 929
inner join propval as Stage_Status on toc.tocid = Stage_Status.tocid and Stage_Status.prop_id = 1276
inner join propval as Phase_of_Review on toc.tocid = Phase_of_Review.tocid and Phase_of_Review.prop_id = 2442
where etype = -2 and pset_id = 500
) as p where p.RIN = '0692-XA001'

table format.png (11.08 KB)

| Download

Group wise query.png (103.37 KB)

| Download

0 0

Replies

replied on September 3, 2024

I think I understand what you want, and unfortunately there isn't a pure-SQL way to accomplish it. The problem is that SQL has very few "aggregate" functions available for you to combine the multiple values of those fields into a single value to be returned. I'm guessing you want to produce something like a comma-separated list, while the only functions available are min, max, sum, etc.

There are work-arounds that might be viable. You can create a temp table where you convert your multi-values into a single value, and then you can join on that. But it will require multiple operations and isn't usable as-is if you need a single query.

1 0

replied on September 7, 2024

Thanks for your answer.

Yes but we certainly required this type of output. Is it from single query or temp table , I don't mind but required same output.

THanks,

pratik

0 0

You are not allowed to follow up in this post.

Question

Question

sql query to arrange template/metadata column wise

Replies

Sign in to reply to this post.