使用iSeries的SQL将多个行值连接为1行 [英] Concatenate Multiple Row Values into 1 Row, with SQL for iSeries
问题描述
首先,我要感谢肯特·米利根(Kent Milligan)及其他在
First, I need to thank Kent Milligan and his article at http://www.mcpressonline.com/sql/techtip-combining-multiple-row-values-into-a-single-row-with-sql-in-db2-for-i.html for getting me as far in this problem as I have. But now I need to expand on what he has done here.
为避免您不得不阅读他的文章,他解决的问题是将字符串数据从多行连接到结果表中的单行中.例如:
To avoid you having to go to his article, the problem he addressed was concatenating string data from multiple rows into a single row in the resulting table. For example:
桌车:
- 制作模型
- 福特Fusion
- 雪佛兰Tahoe
- 本田奥德赛
- 福特金牛座
- 福特福克斯
- Chevy Malibu
结果:
- 制作模型
- 雪佛兰马里布(Tahoe)
- 福特福克斯,金牛座,融合
- 本田奥德赛
这是通过SQL语句完成的:
This was done with the SQL statement:
WITH numbered_sets(make, model, curr, prev) AS (
SELECT make, model,
ROW_NUMBER() OVER (PARTITION BY make ORDER BY model) AS curr,
ROW_NUMBER() OVER (PARTITION BY make ORDER BY model) -1 AS prev
FROM inventory)
SELECT make,
MAX (TRIM(L ',' FROM
CAST(SYS_CONNECT_BY_PATH(model, ',') AS VARCHAR(256)) ))
FROM numbered_sets
START WITH curr = 1
CONNECT BY make = PRIOR make AND prev = PRIOR curr
GROUP BY make
我能够将其调整到我自己的桌子上,并获得大部分想要的方式.但出于我的目的,我需要在分组中添加其他列.例如:
I was able to adapt that to my own table, and get most of the way where I wanted to get. But for my purposes, I have an additional column I need to include for the grouping. For example:
桌车:
- 制作类型模型
- 福特轿车融合
- 雪佛兰SUV Tahoe
- 本田Minivan Odyssey
- 福特三厢金牛座
- 福特轿车福克斯
- Chevy Sedan Malibu
- 福特SUV Escape
- 福特SUV Explorer
- 雪佛兰Sedan Impala
- Make Type Model
- Ford Sedan Fusion
- Chevy SUV Tahoe
- Honda Minivan Odyssey
- Ford Sedan Taurus
- Ford Sedan Focus
- Chevy Sedan Malibu
- Ford SUV Escape
- Ford SUV Explorer
- Chevy Sedan Impala
对于结果,我一直在寻找:
For the Results, I’d be looking for:
- 制作类型模型
- 雪佛兰Sedan Malibu,黑斑羚
- 雪佛兰SUV Tahoe
- 福特轿车Fusion,金牛座,福克斯
- 福特SUV Escape,Explorer
- 本田Minivan Odyssey
有人对我要添加到原始语句中的所有内容有什么想法,以便能够添加TYPE列,然后相应地添加GROUP?我已经尝试了很多方法,但是我怀疑我需要对CONNECT_BY_PATH语句做些事情,只是不确定是什么.
Does anyone have any thoughts on what all I need to add to the original statement to be able to add the TYPE column, and GROUP on that accordingly? I’ve tried a handful of things, but I suspect I need to do something with the CONNECT_BY_PATH statement, I’m just not sure what.
谢谢
推荐答案
我认为您只需要考虑一下查询的正确点就可以集成类型.
I think you just need to integrate type at the correct points thoughtout the query.
在无法测试的情况下,我认为这已经很接近了;但我可能错过了一些东西...
without being able to test, I think this would be close; but I may have missed something...
WITH numbered_sets(make, type, model, curr, prev) AS (
SELECT make, type, model,
ROW_NUMBER() OVER (PARTITION BY make, Type ORDER BY Make, Type, model) AS curr,
ROW_NUMBER() OVER (PARTITION BY make, type ORDER BY Make, type, model) -1 AS prev
FROM inventory)
SELECT make, Type
MAX (TRIM(L ',' FROM
CAST(SYS_CONNECT_BY_PATH(model, ',') AS VARCHAR(256)) ))
FROM numbered_sets
START WITH curr = 1
CONNECT BY make = PRIOR make AND prev = PRIOR curr and type = prior type
GROUP BY make, type
也许我们需要先更改连接方式,然后再进行连接...尽管我尚不清楚为什么这会有所帮助...
Perhaps we need do change the connect by to do a concat before connect by... though I can't see why this would help yet...
CONNECT BY concat(make,type) = PRIOR concat(make,type) AND prev = PRIOR curr
这篇关于使用iSeries的SQL将多个行值连接为1行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!