在SQL Viia递归查询中合并行 [英] Combining Rows in SQL Viia Recursive Query

问题描述

我有下表.

Animal  Vaccine_Date    Vaccine
Cat     2/1/2016        y
Cat     2/1/2016        z
Dog     2/1/2016        z
Dog     1/1/2016        x
Dog     2/1/2016        y

我希望得到的结果如下所示.

I would like to get the results to be as shown below.

Animal  Vaccine_Date    Vaccine
Dog     1/1/2016        x
Dog     2/1/2016        y,z
Cat     2/1/2016        y,z

我有以下代码，该代码是通过我的其他帖子在"组合"中提供的SQL通过基于日期的(连接)行"

I have the following code which was supplied via my other post at "Combine(concatenate) rows based on dates via SQL"

WITH RECURSIVE recCTE AS
(
    SELECT 
        animal, 
        vaccine_date,
        CAST(min(vaccine) as VARCHAR(50)) as vaccine, --big enough to hold concatenated list 
        cast (1 as int) as depth --used to determine the largest/last group_concate (the full group) in the final select
    FROM TableOne

    GROUP BY 1,2


    UNION ALL

    SELECT 
        recCTE.animal,
        recCTE.vaccine_date,
        trim(trim(recCTE.vaccine)|| ',' ||trim(TableOne.vaccine)) as vaccine,
        recCTE.depth + cast(1 as int) as depth
    FROM recCTE 
        INNER JOIN TableOne ON
            recCTE.animal = TableOne.animal AND
            recCTE.vaccine_date =  TableOne.vaccine_date and
           TableOne.vaccine > recCTE.vaccine

           WHERE recCTE.depth < 5


)

--Now select the result with the largest depth for each animal/vaccine_date combo
SELECT * FROM recCTE
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) =1

但这会导致以下结果.

Animal  Vaccine_Date    vaccine     depth
Cat     2/1/2016        y,z,z,z,z   5
Dog     1/1/2016        x           1
Dog     2/1/2016        y,z,z,z,z   5

"z"不断重复.这是因为该规范说的任何内容都比最低疫苗要大.为了解决这个问题，将代码更改为以下代码.

The "z" keeps repeating. This is because the code is saying anything greater than the minimum vaccine. To account for this, the code was changed to the following.

WITH RECURSIVE recCTE AS
(
    SELECT 
        animal, 
        vaccine_date,
        CAST(min(vaccine) as VARCHAR(50)) as vaccine, --big enough to hold concatenated list 
        cast (1 as int) as depth, --used to determine the largest/last group_concate (the full group) in the final select
        vaccine as vaccine_check
    FROM TableOne

    GROUP BY 1,2,5


    UNION ALL

    SELECT 
        recCTE.animal,
        recCTE.vaccine_date,
        trim(trim(recCTE.vaccine)|| ',' ||trim(TableOne.vaccine)) as vaccine,
        recCTE.depth + cast(1 as int) as depth,
        TableOne.vaccine as vaccine_check
    FROM recCTE 
        INNER JOIN TableOne ON
            recCTE.animal = TableOne.animal AND
            recCTE.vaccine_date =  TableOne.vaccine_date and
           TableOne.vaccine > recCTE.vaccine and
           vaccine_check <> recCTE.vaccine_check 

           WHERE recCTE.depth < 5


)

--Now select the result with the largest depth for each animal/vaccine_date combo
SELECT * FROM recCTE
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) =1

但是，这导致了以下结果.

However, this resulted in the following.

Animal  Vaccine_Date    vaccine depth   vaccine_check
Cat     2/1/2016        y       1       y
Dog     1/1/2016        x       1       x
Dog     2/1/2016        y       1       y

为获得所需的以下结果，代码中缺少的内容.

What is missing in the code to get the desired results of the following.

Animal  Vaccine_Date    Vaccine
Dog     1/1/2016        x
Dog     2/1/2016        y,z
Cat     2/1/2016        y,z

推荐答案

我能够使用以下SQL来获得所需的结果.这似乎根本不是很有效，也不是动态的.但是，我可以根据需要添加额外的子查询，以按日期按动物组合更多疫苗.

I was able to get the desired results with the following SQL. This doesn't seem very efficient at all and is not dynamic. However, I can add extra sub querys as needed to combine more vaccines by animal by date.

select 
qrya.animal
,qrya.vaccine_date
,case when qrya.vac1 is not null then qrya.vac1 else null end ||','||case when qrya.animal=qryb.animal and qrya.vaccine_date=qryb.vaccine_date then qryb.Vac2 else 'End' end as  vaccine_List
from
(
select

qry1.Animal
,qry1.Vaccine_Date

,case when qry1.Vaccine_Rank = 1  then qry1.vaccine end as Vac1


from
(
select 
animal
,vaccine_date
,vaccine
,row_number() over (partition by animal,vaccine_date order by vaccine) as Vaccine_Rank
from TableOne
) as qry1

where vac1 is not null

group by qry1.Animal,
qry1.Vaccine_Date
,case when qry1.Vaccine_Rank = 1  then qry1.vaccine end 
) as qrya
join
(
select

qry1.Animal
,qry1.Vaccine_Date

,case when qry1.Vaccine_Rank = 2  then qry1.vaccine end as Vac2


from
(
select 
animal
,vaccine_date
,vaccine
,row_number() over (partition by animal,vaccine_date order by vaccine) as Vaccine_Rank
from TableOne
) as qry1

where vac2 is not null

group by qry1.Animal,
qry1.Vaccine_Date
,case when qry1.Vaccine_Rank = 2  then qry1.vaccine end 
) as qryb
on qrya.Animal=qryb.Animal

这篇关于在SQL Viia递归查询中合并行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！