The issue I'm having is that I would like it to be in one row for each city code instead of multiple rows. I would like this done on the query side. I have tried using a repeater on the city_name column but it doesn"t work...
| city_code | city_sequence | city_name |
|---|---|---|
| 3051715 | 1 | London |
| 3051715 | 2 | New York |
| 3051715 | 3 | San Francisco |
| stock_code | stock_note |
|---|---|
| 3051715 | London, New York, San Francisco |
Thanks,
If you really must do this in a query, you'll need to know the maximum value of city_sequence before creating the data items. You'll pre-pivot the results into columns (city1, city2, city3, etc.) by using case statements like this:
case
when [city_sequence] = 1
then [city_name]
end
Set the detail aggregation to maximum.
If you ever have more cities than you expected, you'll miss some.
And good luck getting the commas right.
Without seeing the error message you received, I can't help with that. But I usually find that I don't need to do this in the query.
I have done this using a repeater table (not a repeater) that's 1 row by x columns. You need to know (or estimate) the maximum number of values you'll have on one line and make sure you have at least that many columns in the repeater table. It's OK to have more columns than you need in the repeater table.
Here is a sample report spec:
<report xmlns="http://developer.cognos.com/schemas/report/15.5/" expressionLocale="en-us" useStyleVersion="11.4">
<!--RSU-SPC-0093 The report specification was upgraded from "http://developer.cognos.com/schemas/report/14.3/" to "http://developer.cognos.com/schemas/report/15.5/" at 2021-3-25. 18:9:49-->
<drillBehavior/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents>
<list horizontalPagination="true" name="List1" refQuery="Query3">
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemLabel refDataItem="Country"/>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Country"/>
</dataSource>
</textItem>
</contents>
</listColumnBody>
</listColumn>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<staticValue>Retailer Type</staticValue>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<repeaterTable across="20" down="1" name="Repeater table1" refQuery="Query2">
<repeaterTableCell>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Retailer type"/>
</dataSource>
</textItem>
<textItem>
<dataSource>
<dataItemValue refDataItem="Comma"/>
</dataSource>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="rc"/>
</defaultStyles>
<CSS value="padding:0px;font-size:8pt"/>
</style>
</repeaterTableCell>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="rt"/>
</defaultStyles>
</style>
<masterDetailLinks>
<masterDetailLink>
<masterContext>
<dataItemContext refDataItem="Country"/>
</masterContext>
<detailContext>
<dataItemContext refDataItem="Country"/>
</detailContext>
</masterDetailLink>
</masterDetailLinks>
</repeaterTable>
</contents>
</listColumnBody>
</listColumn>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemLabel refDataItem="Quantity"/>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lm"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Quantity"/>
</dataSource>
</textItem>
</contents>
</listColumnBody>
</listColumn>
</listColumns>
</list>
</contents>
</pageBody>
<XMLAttributes>
<XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/>
</XMLAttributes>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes>
<XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/>
<XMLAttribute output="no" name="listSeparator" value=","/>
<XMLAttribute output="no" name="decimalSeparator" value="."/>
<XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/>
</XMLAttributes>
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO sales (query)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection>
<dataItem aggregate="none" name="Country" rollupAggregate="none" sort="ascending">
<expression>[Sales (query)].[Branch].[Country]</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="3"/>
<XMLAttribute name="RS_dataUsage" output="no" value="attribute"/>
</XMLAttributes>
</dataItem>
<dataItem aggregate="none" name="Retailer type" rollupAggregate="none" sort="ascending">
<expression>[Sales (query)].[Retailer type].[Retailer type]</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="3"/>
<XMLAttribute name="RS_dataUsage" output="no" value="attribute"/>
</XMLAttributes>
</dataItem>
<dataItem aggregate="total" name="Quantity">
<expression>[Sales (query)].[Sales].[Quantity]</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="1"/>
<XMLAttribute name="RS_dataUsage" output="no" value="fact"/>
</XMLAttributes>
</dataItem>
</selection>
<detailFilters>
<detailFilter>
<filterExpression>[Sales (query)].[Time].[Year] = 2013</filterExpression>
</detailFilter>
</detailFilters>
</query>
<query name="Query2">
<source>
<queryRef refQuery="Query1"/>
</source>
<selection>
<dataItem aggregate="none" name="Country" rollupAggregate="none">
<expression>[Query1].[Country]</expression>
</dataItem>
<dataItem aggregate="none" name="Retailer type" rollupAggregate="none">
<expression>[Query1].[Retailer type]</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="3"/>
<XMLAttribute name="RS_dataUsage" output="no" value="0"/>
</XMLAttributes>
</dataItem>
<dataItem name="Comma">
<expression>case
when running-count ([Retailer type] for [Country]) < maximum (running-count ([Retailer type] for [Country]) for [Country]) then ','
else ''
end</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="3"/>
<XMLAttribute name="RS_dataUsage" output="no" value=""/>
</XMLAttributes>
</dataItem>
</selection>
</query>
<query name="Query3">
<source>
<queryRef refQuery="Query1"/>
</source>
<selection>
<dataItem aggregate="none" name="Country" rollupAggregate="none">
<expression>[Query1].[Country]</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="3"/>
<XMLAttribute name="RS_dataUsage" output="no" value="0"/>
</XMLAttributes>
</dataItem>
<dataItem aggregate="total" name="Quantity">
<expression>[Query1].[Quantity]</expression>
<XMLAttributes>
<XMLAttribute name="RS_dataType" output="no" value="2"/>
<XMLAttribute name="RS_dataUsage" output="no" value="2"/>
</XMLAttributes>
</dataItem>
</selection>
</query>
</queries>
<reportName>Repeater Table (using Samples data)</reportName>
</report>
depending on the database, use a function like listagg or xmlagg
https://www.ibm.com/docs/en/db2/11.5?topic=functions-listagg
For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column
For example,
SELECT LISTAGG(trim(City_Name),',') FROM MyTable
another stackoverflow link may help
How to concatenate text from multiple rows into a single text string in SQL Server
