I know that I will use stuff and for xml for this process, but in this way, the sentence like the one below becomes too long. Unfortunately I don't actually have a table like "controlTable". I actually write a long sentence to generate the controlTable and connect it to my main sql statement with "outer apply".

select @@version;

| (No column name) | | :----------------| | Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64)
Oct 18 2021 09:37:01
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 \ (Build 17763: ) (Hypervisor)
|

CREATE TABLE [dbo].[controlTable](
	[category] [nvarchar](50) NULL,
	[control1] [int] NULL,
	[control2] [int] NULL,
	[control3] [int] NULL,
	[control4] [int] NULL,
	[control5] [int] NULL,
	[control6] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Accessories', 19, 0, 12, 0, 0, 7)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Pocket', 6, 0, 5, 0, 0, 1)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Button', 28, 0, 27, 0, 1, 0)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Women', 25, 0, 24, 0, 0, 1)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Men', 15, 0, 11, 0, 2, 2)

status
5 rows affected

select * from controlTable

| category | control1 | control2 | control3 | control4 | control5 | control6 | | :--------|--------:|--------:|--------:|--------:|--------:|--------:| | Accessories | 19 | 0 | 12 | 0 | 0 | 7 | | Pocket | 6 | 0 | 5 | 0 | 0 | 1 | | Button | 28 | 0 | 27 | 0 | 1 | 0 | | Women | 25 | 0 | 24 | 0 | 0 | 1 | | Men | 15 | 0 | 11 | 0 | 2 | 2 |

/*
select 
STRING_AGG(category,'|')  as 'category'
,STRING_AGG(control1,'|')  as 'control1'
,STRING_AGG(control2,'|') as 'control2'
,STRING_AGG(control3,'|') as 'control3'
,STRING_AGG(control4,'|') as 'control4'
,STRING_AGG(control5,'|') as 'control5'
,STRING_AGG(control6,'|') as 'control6'
from controlTable


Accessories|Pocket|Button|Women|Men	19|6|28|25|15	0|0|0|0|0	12|5|27|24|11	0|0|0|0|0	0|0|1|0|2	7|1|0|1|2
*/

select 

(
select 
STUFF((select '|' + rtrim(replace(ct.category,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'category'

,(
select 
STUFF((select '|' + rtrim(replace(ct.control1,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control1'


,(
select 
STUFF((select '|' + rtrim(replace(ct.control2,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control2'


,(
select 
STUFF((select '|' + rtrim(replace(ct.control3,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control3'



,(
select 
STUFF((select '|' + rtrim(replace(ct.control4,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control4'



,(
select 
STUFF((select '|' + rtrim(replace(ct.control5,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control5'



,(
select 
STUFF((select '|' + rtrim(replace(ct.control6,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control6'

| category | control1 | control2 | control3 | control4 | control5 | control6 | | :--------|:--------|:--------|:--------|:--------|:--------|:--------| | Accessories\|Pocket\|Button\|Women\|Men | 19\|6\|28\|25\|15 | 0\|0\|0\|0\|0 | 12\|5\|27\|24\|11 | 0\|0\|0\|0\|0 | 0\|0\|1\|0\|2 | 7\|1\|0\|1\|2 | [fiddle](https://dbfiddle.uk/d9DIWVAT)

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record. CREATE TABLE HashTable( RecordID VARCHAR(255), GroupIdentifier VARCHAR(255), Hash VARCHAR (255), GroupHashList VARCHAR(4000) ) (I know this isn't an efficient table, but it's just a temp table for the purposes of this example). I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group. I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost. SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC) FROM HashTable GROUP BY [GroupIdentifier] This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly: UPDATE HashTable SET GroupHashList = c.HashList FROM HashTable INNER JOIN ( SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList], [GroupIdentifier] FROM HashTable GROUP BY [GroupIdentifier]) c ON c.[GroupIdentifier] = HashTable.[GroupIdentifier] Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing? What I'm getting the first time is Hash1; Hash2; Hash3 Hash1; Hash2; Hash3 But when it's in the UPDATE query, I get Hash1; Hash2; Hash3 Hash1; Hash3; Hash2 The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

A sql query yields data that looks like this: | name | quality | magnitude | | ----------- | ----------- | ----------- | | john | kindness | 7 | john | greed | 2 | jane | kindess| 3 | | jane | greed | 7 | | john | temper | 9 | | jane | temper | 4 | I am wondering if there is a way to transform it into data that looks like this using SQL: | name | personality | | ----------- | ----------- | ----------- | | john | kindess:7, greed:2, temper:9 | | jane | kindess: 3, greed: 7, temper: 4 | If yes: 1. How do it do it? 2. Is this an optimal/good idea? I am on Postgres 9+

I have a relation where a User has multiple dogs (as many as 15) but each dog is contained in a single row in the table, and they all have a userId in common. For example, Table dogs: | User | Dog Name | Age | ABCD | Fido | 7 | ABCD | Gooofy | 9 | ABCD | Toto | 4 | ABCD | Roger | 12 | ABCD | Barkley | 3 I would like to do something like SELECT * FROM dogs GROUP BY User where the result will output a csv of ABCDs dogs, as follows: ABCD, Fido, 7, Goofy, 9, Toto, 4, Roger, 12, Barkley, 3 I'm using BigQuery but I think knowing how to do this in PostgreSQL may even be helpful. So maybe I can create some sort of view or something?

Let's say I have table #DOC like this: |polNum |boxNo |batchNum| |-------|------|--------| |111111 | null | qwe1234| |111111 | ff/55| rto1235| |111111 | ee/59| yhn1238| |111111 | ww/55| rto1235| |222222 | dd/58| jkl1234| |222222 | null | fgh1234| |333333 | null | asz1235| |444444 | ff/55| edc1234| |444444 | tt/58| qaz1234| This table doesn't have any primary key. We can assume that polNum and boxNo pair are unique. I need have collection from batchNum and boxNo become like this: |polNum |boxNo |batchNum| |-------|------|--------| |111111 | null | | |111111 | ff/55| qwe1234,rto1235,yhn1238| |111111 | ee/59| qwe1234,rto1235,yhn1238| |111111 | ww/55| qwe1234,rto1235,yhn1238| |222222 | dd/58| jkl1234,fgh1234| |222222 | null | | |333333 | null | asz1235| |444444 | ff/55| edc1234,qaz1234| |444444 | tt/58| edc1234,qaz1234| Need to manipulate batchNum row with comma separated based polNum and not empty boxNo and put same row. But if any condition boxNo is empty or null batchNum will put same polNum row. I was tried using stuff approach, result still goes wrong:

SELECT DISTINCT polNum, boxNo ,
    STUFF((
        SELECT DISTINCT ','+batchNum
        FROM #DOC a
        WHERE a.polNum=d.polNum or a.boxNo = d.boxNo
        FOR XML PATH('')
    ),1,1,'') batchNum
FROM #DOC d

|polNum|boxNo |batchNum| |-------|------|--------| |111111 | null | qwe1234,rto1235,yhn1238| |111111 | ff/55| qwe1234,rto1235,yhn1238| |111111 | ee/59| qwe1234,rto1235,yhn1238| |111111 | ww/55| qwe1234,rto1235,yhn1238| |222222 | dd/58| jkl1234,fgh1234| |222222 | null | jkl1234,fgh1234| |333333 | null | asz1235| |444444 | ff/55| edc1234| |444444 | tt/58| qaz1234| batchNum is not always be null, depends on value of boxNo and polNum. Null row have values and some not. depend on BoxNo value itself.

I have a table of people : id, person_name I have a table of tags: id, tag_name there is a junction table for the tags that connects people and tags: id, person_id, tag_id I have a query to get a group of people based on different search criteria so it's being built up dynamically and what I would like to do is within that query - for each person - I'd like to be able to grab all of the tag names associated w/ that person. I'd like to grab all the associated tags and stick them into one column for each person row (preferably as an array or object). What is the general pattern to be able to return a group of stuff as one column when cycling through rows like this. I'm having trouble figuring out what to search for to find a generalized answer online. I'm using postgres.

I have an SP that flattens a bunch of tables. I recently refactored so that instead of a single description in one table I now have a one to many relationship with a descriptions table. I can inner join and get the descriptions on many rows, but I'd rather just concatenate the descriptions into a single field. I'm trying STRING_AGG, but I don't think it works for my purposes. Here's the working query that just gives me each description on its own row and commented out what I tried to concatenate (adding a comma to the end of the line above as well). I don't really grok SQL well enough to even know how to proceed . . . Select P.ID as ProjectID, P.Code, P.MOAProjectname, P.Contractorname, P.Ownername, M.ID as PeopleID, M.Firstname + ' ' + M.Lastname As Fullname, R.ID as ReportID, R.Reportnumber, R.Reportdate, R.Weather, R.Temperature, R.Presentatsite, R.Estimatedcomplete, R.Progress, R.Trades, R.Note, D.DivID, D.DivName, O.ID as ObsID, O.Description, O.PicCaption, O.Opendate, O.Closedate, O.ImagePath, O.ObservationNum --STRING_AGG (T.Description,', ') AS Dsc From Observations as O inner Join Reportinformation as R on O.ReportID = R.ID Inner Join Descriptions as T on O.ID = T.ObsID Inner Join Divisions as D on O.Division = D.DivID Inner Join MOApeople as M on M.ID = R.MOApeopleID Inner Join Projectinformation as P on P.ID = R.ProjectID Order By P.Code, O.Division, O.ObservationNum I am guessing that I could create a temp table that does the aggregation, and then join that table into my main query. Is that the right direction to pursue?

**Question**, can I add escape character \ just for fields param_name and value for this? string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params", Result is. {name:"připojení",value:"1/2""}, I need this (use \ before " only for fields/vars inside "param_name" and "value", in case, when value has " character. {name:"připojení",value:"1/2\""}, Thanks. **Update**, no json, I need export to csv. Some code here. copy( with recursive cte as( select category_id, category_parent, category_name::text, category_id::text category_ids from s_category as c where category_parent = 0 union all select c.category_id, c.category_parent, concat(cte.category_name, ' > ', c.category_name), concat(cte.category_ids, ':', c.category_id::text) from s_category as c,cte where cte.category_id = c.category_parent ) select distinct s_product.product_id as "itemID", ... string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params", case when price_tax = price_rec then '' else 'Akce' end as "label" from s_product left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id left join s_product_image on s_product.product_id = s_product_image.product_id left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id left join s_producer on s_product.producer_id = s_producer.producer_id left join s_category on s_product.category_id = s_category.category_id left join cte on s_product.category_id = cte.category_id left join s_cf_j_product_value as a on s_product.product_id = a.product_id left join s_cf_value as v on a.value_id = v.value_id left join s_cf_param as p on v.param_id = p.param_id where ... group by s_product.product_id, s_product_image.filename, s_category.category_id, s_pricelist_generated_lists.price_tax, s_pricelist_generated_lists.price_rec, s_producer.producer_name, cte.category_name, cte.category_ids ) TO stdout DELIMITER ',' CSV; It returns this. ... {name:""připojení"",value:""1/2""""},{name:""připojovací rozteč"",value:""558 mm""},{name:""rozměry"",value:""600 x 960 mm""}","" But I need this. ... {name:"připojení",value:"1/2\""},{name:"připojovací rozteč",value:"558 mm"},{name:"rozměry",value:"600 x 960 mm"}","

I'm new to ORACLE and I'm trying to use a string aggregation function in my query to concatenate the value of rows with a comma , this is the structure of my tables: 1. Student(STUDENT_ID,student_name,age) 2. Course(course_no,description) 3. Student_Course(student_id,course_id,nomreh) description column indicates the name of the course for example Algebra or Math. Nomreh column shows the Mark each student achieved in each course (it's a Persian word). This is my query Select s.student_name , LISTAGG(c.description, ',') WITHIN GROUP (ORDER BY c.description) AS Courses, LISTAGG(sc.nomreh, ',') WITHIN GROUP (ORDER BY sc.nomreh) AS Ranks from student s inner join student_course sc on s.student_id = sc.student_id inner join course c on sc.course_id = c.course_no group by s.student_name I want the output to show: [Student-name] [Course-Description] [Nomre] Artin Algebra,Math,Sport 10,11,12 each value in [Nomreh] column should be exactly for that [Course] column and for that specific student . Unfortunately my query does not give the desired output and the values in Nomreh column are not in correct order with [Course-Description] column.

I have a query who returns the next results +-----------+--------+-------+ | idPeriodo | codigo | valor | +-----------+--------+-------+ | 1 | 7 | 1000 | | 1 | 8 | 1000 | | 1 | 9 | 1000 | | 2 | 7 | 1000 | | 2 | 8 | 1000 | | 3 | 7 | 1000 | | 3 | 9 | 1000 | +-----------+--------+-------+ I need to organize the query because I'm creating a text plain file with the results, so in the first row I need to put each "codigo" split by tab, something like this. --This is the first row, is like a head 7 8 9 Afther this, I need to put the column "valor" associated with its respective codigo and idPeriodo. The final result must be something like this: 7 8 9 1 1000 1000 1000 2 1000 1000 3 1000 1000 Now I have two queries, the first one is to obtain the first row(head with codigo values) and second one I'm doing a query to obtain all values from column "valor" grouped by idPeriodo. These are my queries looks like. -------Query to return head(codigo values)--------------- SELECT DISTINCT CONCAT(conjunto.codigo, central.confterm_id) as codigo_central FROM mantenimiento_central_termica mantenimiento JOIN configuracion_central_termica central ON central.confterm_id = mantenimiento.confterm_id JOIN conjunto_centrales conjunto ON central.id_conjuntocentral = conjunto.id -------Query to return valor grouped by idPeriodo--------------- SELECT id_periodo , valor_mantenimiento = string_agg(valor_mantenimiento, CHAR(9)) WITHIN GROUP (ORDER BY codigo_central) FROM( SELECT CONCAT(conjunto.codigo, central.confterm_id) as codigo_central, per_id AS id_periodo , mantenimcentraltermica_potencia AS valor_mantenimiento FROM mantenimiento_central_termica mantenimiento JOIN configuracion_central_termica central ON central.confterm_id = mantenimiento.confterm_id JOIN conjunto_centrales conjunto ON central.id_conjuntocentral = conjunto.id WHERE cas_id = 2) main_thermal GROUP BY id_periodo The problem I have is the second query returns only the values from column valor who is not null and when I'm going to split the first query with the second one I obtain something like this: 7 8 9 1 1000 1000 1000 2 1000 1000 3 1000 2000 --The error is here, because the 2000 value must be in the third column, with the 9(codigo) value. I need the query put a blank space when find a null valor in a relation between codigo and idPeriodo. How Can i do that ?

I'm migrating a schema from NoSQL to postgres. Today's object "b" exists in NoSQL as an element of object a & looks in source like this...

{
  "a": {
    "id": 1,
    "b":{
      "c1": {
        "d1": 1,
        "d2": 2
      },
      "c2": {
        "d1": 1,
        "d2": 2
      },
      "c3": {
        "d1": 3,
        "d2": 4
      }
    }
  }
}

...which I've tentatively assigned to a schema resembling the below ERD

+---+    +-----+    +---+    +---+
| b |-->| d |>-->| c |
+---+    +-----+    +---+    +---+

full db_fiddle I'd like to be able to recompose the normalised data into the original object but I've been unable to do so. Instead of getting "c" objects as direct key-value pairs, I'm getting whole objects. For example ...

select 
    b_d.b_id, 
    json_agg(json_build_object(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns...

[{
    "c1 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c2 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}]

...which you will note has an additional nesting level versus the source object. I suspect I've gone wrong out of the gate with json_build_object but so far have been unable to successfully reconstruct the "b" object as it appears in the original json document. What do I modify about the query and/or the underlying schema to normalize and denormalize this object?

create table dbo.CourseMaster ( CourseId char(2), CourseName char(3) ); create table dbo.StudentMaster ( ROLLNO char(5), NAME varchar(10), ADDRESS varchar(20), Course varchar(100) ); insert into dbo.CourseMaster values ('01', 'ABC'), ('02', 'DEF'), ('03', 'GHI'), ('04', 'JKL'), ('05', 'MNO'), ('06', 'PQR'), ('07', 'STU'); insert into dbo.StudentMaster values ('12345', 'RAM', 'RAM ADDRESS', '01,02,02'), ('25695', 'HARI', 'HARI ADDRESS', '02,06'), ('89685', 'JEFF', 'JEFF ADDRESS', '03,05,06,07'), ('47896', 'DAISY', 'DAISY ADDRESS', '03'); Query 1: select SM.ROLLNO, SM.NAME, SM.ADDRESS, ( select ','+CM.CourseName from dbo.CourseMaster as CM where ','+SM.Course+',' like '%,'+CM.CourseId+',%' for xml path(''), type ).value('substring(text(), 2)', 'varchar(max)') as Course from dbo.StudentMaster as SM; Actual Ouput: | ROLLNO | NAME | ADDRESS | Course | |--------|-------|---------------|-----------------| | 12345 | RAM | RAM ADDRESS | ABC,DEF | | 25695 | HARI | HARI ADDRESS | DEF,PQR | | 89685 | JEFF | JEFF ADDRESS | GHI,MNO,PQR,STU | | 47896 | DAISY | DAISY ADDRESS | GHI | When I add course like 01,02,02 in student master it give below output: ABC,DEF and it should give: ABC,DEF,DEF

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I'd like to "flatten"/"merge" all rows with the same ID, pretty much as is well delineated here: https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php However, in this example the "concatenation" is applied only to one field/column; I'd want to apply it to **several fields/columns**. Also, I'd only want to concatenate **unique/distinct** entries: [In the above example](https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php) that would be

  P002   | CA003

instead of the duplication

 P002   | CA003,CA003

Finally, for the "Relevant"-column, I'd like to only have "Yes" in there, iff at least one row with a given ID has a "Yes" (so more than "just" concatenation required here... probably something different, e.g. "if-then"-equivalent). In other words, it's enough if 1 person thinks it's relevant to make it relevant (in the flattened table). ----------- Related links: https://dba.stackexchange.com/questions/696/eliminate-duplicates-in-listagg-oracle https://livesql.oracle.com/apex/livesql/file/content_HT1O85E4BHSBWN93G1B3M8SI2.html https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values https://stackoverflow.com/questions/17639655/2-listagg-in-one-sql-select-in-oracle https://modern-sql.com/feature/listagg ------- - SQLFiddle (for the "input"): http://www.sqlfiddle.com/#!4/fd02c/11 And the raw data:

| ID | Relevant | LongDescription |      A |      B |      C |                           Comment |                         FurtherDetails |
|----|----------|-----------------|--------|--------|--------|-----------------------------------|----------------------------------------|
|  1 |      Yes |   text for ID 1 |    Yes | (null) |    Yes |    This is a Tony’s comment for 1 |    Further details on this 1 from Tony |
|  2 |       No |   text for ID 2 | (null) |    Yes | (null) |    This is Andrew’s comment for 2 |  Further details on this 2 from Andrew |
|  2 |      Yes |   text for ID 2 | (null) | (null) | (null) |      This is Mary’s comment for 2 |    Further details on this 2 from Mary |
|  3 |      Yes |   text for ID 3 | (null) | (null) | (null) |                            (null) |                                 (null) |
|  4 |   (null) |   text for ID 4 | (null) | (null) | (null) |    This is George’s comment for 4 |  Further details on this 4 from George |
|  2 |   (null) |   text for ID 2 |    Yes |    Yes | (null) |                            (null) |                                 (null) |
|  7 |       No |   text for ID 7 | (null) | (null) | (null) |                            (null) |                                 (null) |
|  1 |       No |   text for ID 1 | (null) | (null) | (null) | This is a Tiffany’s comment for 1 | Further details on this 1 from Tiffany |
|  1 |      Yes |   text for ID 1 | (null) |    Yes | (null) |                            (null) |     Further details on this 1 from Sam |

------- - SQLFiddle (for the desired "output"): http://www.sqlfiddle.com/#!4/fd02c/10 And the raw data:

| ID | Relevant | LongDescription |      A |      B |      C |                                                             Comment |                                                                                                                  FD | RowCount |
|----|----------|-----------------|--------|--------|--------|---------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------|----------|
|  1 |      Yes |   text for ID 1 |    Yes |    Yes |    Yes | This is a Tiffany’s comment for 1 // This is a Tony’s comment for 1 | Further details on this 1 from Sam // Further details on this 1 from Tiffany // Further details on this 1 from Tony |        3 |
|  2 |      Yes |   text for ID 2 |    Yes |    Yes | (null) |      This is Andrew’s comment for 2 // This is Mary’s comment for 2 |                                        Further details on this 2 from Andrew // Further details on this 2 from Mary |        3 |
|  3 |      Yes |   text for ID 3 | (null) | (null) | (null) |                                                              (null) |                                                                                                              (null) |        1 |
|  4 |   (null) |   text for ID 4 | (null) | (null) | (null) |                                      This is George’s comment for 4 |                                                                               Further details on this 4 from George |        1 |
|  7 |       No |   text for ID 7 | (null) | (null) | (null) |                                                              (null) |                                                                                                              (null) |        1 |

I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query. SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as topic, COUNT(c.topic_id) as count FROM post_topics a JOIN posts b ON a.post_id = b.id JOIN post_topics c on a.post_id = c.post_id JOIN topics t on c.topic_id = t.id --with test as (select id from topics) --WHERE (a.topic_id::varchar) in (select id from topics_temp) **WHERE (a.topic_id::varchar) in (SELECT STRING_AGG(id, ',') FROM topics_temp) AND (t.id::varchar) NOT IN (SELECT STRING_AGG(id, ',') FROM topics_temp)** --AND (t.id::varchar) NOT IN (select id from topics_temp) and (b.date_posted > (('now'::text)::date - '6 mons'::interval)) GROUP BY t.id, c.topic_id,a.topic_id ORDER BY count DESC; Result set: Original query: I am passing topic id directly in the below query and counts is different. SELECT t.id as topic_id, t.value as value, t.topic as topic, COUNT(c.topic_id) as count FROM post_topics a JOIN posts b ON a.post_id = b.id JOIN post_topics c on a.post_id = c.post_id JOIN topics t on c.topic_id = t.id --AND t.topic = 'cuisine' WHERE a.topic_id = 'c108200f-e4dc-415e-9150-3f6c74b879e2' AND t.id != 'c108200f-e4dc-415e-9150-3f6c74b879e2' AND (b.date_posted > (('now'::text)::date - '6 mons'::interval)) GROUP BY t.id, c.topic_id ORDER BY count DESC LIMIT 10; SO in the where clause I have mentioned two conditions with IN and Not IN condition. Topics_temp is having 8110 distinct topic name where I should perform the above joins in each topic. But I dont know how to perform the above logic for each topic in query. Can anyone help me on this please. I am trying harder for last two days to crack this down. Query Logic Original query: Table information: Posts: post_id, url, dateposted |Topics: topic_id,topic,value |Post_topic: post_id,topic_id First it get topic id from where clause and starts the first join with post table - it selects all posts relevant to that topic id. In the second join - for the retrieved posts from previous query it goes to post_topics table to get relevant topics again. Now in the third join it calculates the count for each topic that we got from second join. The output for this topic id is 'c108200f-e4dc-415e-9150-3f6c74b879e2' shared already. And the first column in the output is associated topic id that we got my third join.

I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error: > ERROR: column "da2.dependency_device_name" must appear in the GROUP > BY clause or be used in an aggregate function. Here is the sql. I have added the field to the group by and still have the same error message: Select xy1."Dependency Count", xy1."Dependency List", ag2.report_type_name, xy1.dependent_device_name from view_affinitygroup_v2 ag2 left join ( Select da2.dependency_device_name, da2.effective_to, da2.dependent_device_name, da2.dependent_device_fk, sum(1) "Dependency Count", string_agg(da2.dependency_device_name,', ') "Dependency List" from view_deviceaffinity_v2 da2 ) as xy1 on xy1.dependent_device_fk = ag2.primary_device_fk and xy1.effective_to is null where ag2.report_type_id = 1 group by da2.dependency_device_name, ag2.report_type_name, xy1.dependent_device_name, xy1.effective_to

I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do. CREATE TABLE SPD_OWNER.EMP ( EMPID NUMBER, NAME VARCHAR2(20), MGRID NUMBER, DEPT VARCHAR2(5) ); INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT'); INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT'); INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT'); INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT'); INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT'); -- query which has issue WITH tmp (rnum,dept,id) AS (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)), cte (rnum,dept,id) AS (SELECT rnum, dept, id FROM tmp WHERE rnum=1 UNION ALL SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1) SELECT * FROM cte cte1 WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept); Am expecting below output. 5 IT 1/2/3/4/5 But running above query gives me an error SQL Error : ORA-25153: Temporary Tablespace is Empty Isn't the condition tmp.rnum=cte.rnum+1 sufficient to break the recursive loop? Here's what am imagining to be happening. 1. 1st select (above union all) will select rnum=1 2. 1st recursive iteration will select tmp.rnum=cte.rnum+1, i.e. tmp.rnum=2 3. 2nd recursive iteration will select tmp.rnum=3 4. and so on till this condition is invalid, i.e. when cte.rnum=5 (because then tmp.rum=5+1 will not have matching record) Am assuming that am getting the error since it's going into infinite loop. What am i missing? Side Note: Am using Oracle DB, so i can easily do this using LISTAGG. But i also want to run this query in H2 DB. So i want to have a solution that uses standard sql. Am open to other ways to achieve the same result.