how to aggregate values from a pivot?

Question

my query returns a dataset that looks like this:

+-----------+--------+-----------+-------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+---------+------------+--------+---------+------------+---------+---------+------------+--------+
| CLIENT_ID | count1 | TestFreq1 | stdv1 | count2 | TestFreq2 |  stdv2  | count3 | TestFreq3 |  stdv3  | count4 | TestFreq4 |  stdv4  | count5 | TestFreq5 |  stdv5  | count6 | TestFreq6 |  stdv6  | count7 | TestFreq7 |  stdv7  | count8 | TestFreq8 |  stdv8  | count9 | TestFreq9 |  stdv9  | count10 | TestFreq10 | stdv10 | count11 | TestFreq11 | stdv11  | count12 | TestFreq12 | stdv12 |
+-----------+--------+-----------+-------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+---------+------------+--------+---------+------------+---------+---------+------------+--------+
|    210893 |    136 |         0 |     0 |     81 |        41 | 79.2685 |     19 |        63 | 58.321  |     24 |        21 | 20.4896 |      5 |        25 | 8.228   |      6 |        24 | 24.0638 |      4 |        25 | 24.6103 | 2      | 25        | 2.12132 |      2 |        23 | 21.9203 | 1       | 33         | NULL   |       2 |         29 | 7.77817 | 1       | 38         | NULL   |
|    123321 |     50 |         0 |     0 |      5 |        26 | 7.87401 |     14 |        45 | 51.8002 |      3 |        25 | 14.7422 |      2 |        22 | 17.6777 |      4 |        36 | 21.4942 |      3 |        36 | 22.2711 | NULL   | NULL      | NULL    |      4 |        35 | 9.30949 | NULL    | NULL       | NULL   |       1 |         31 | NULL    | NULL    | NULL       | NULL   |
|    454322 |    232 |         0 |     0 |    173 |        10 | 33.8487 |     36 |        36 | 36.6602 |     32 |        15 | 17.485  |     10 |        38 | 22.4809 |     13 |        23 | 20.0477 |      7 |        18 | 11.4143 | 3      | 32        | 24.5425 |      6 |        25 | 16.8602 | 3       | 28         | 21.166 |       2 |         25 | 4.94975 | 1       | 34         | NULL   |
+-----------+--------+-----------+-------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+---------+------------+--------+---------+------------+---------+---------+------------+--------+

instead of forcing the data to go out to count13, stdv13, testfreq13, ..14..14..14, 15.15.15 how can i aggregate all the values 12 and over in the same field?

here's my query, and thank you so much for your guidance:

;WITH counted AS (
  SELECT
    client_id,
    COUNT(*) AS TimesTested,
    (datediff(day,MIN(received_date),max(received_date)))
  /COUNT(*) as TestFreq
  FROM f_accession_daily
  GROUP BY
    client_id,
    patient_id
),
counted2 as (
  SELECT
    client_id,
    TimesTested,
    CAST(COUNT(*) AS varchar(30)) AS count,
    CAST(AVG(testfreq) as varchar(30)) as TestFreq,
    CAST(STDEV(TestFreq) as varchar(30)) Stdv
  FROM counted
  GROUP BY
    client_id,
    TimesTested
    )
    ,
unpivoted AS (
  SELECT
    client_id,
    ColumnName + CAST(TimesTested AS varchar(10)) AS ColumnName,
    ColumnValue
  FROM counted2
  UNPIVOT (
    ColumnValue FOR ColumnName IN (count, TestFreq,stdv)
  ) u
),
pivoted AS (
  SELECT
    client_id clientid,
    count1, TestFreq1,stdv1,
    count2, TestFreq2,stdv2,
    count3, TestFreq3,stdv3,
    count4, TestFreq4,stdv4,
    count5, TestFreq5,stdv5,
    count6, TestFreq6,stdv6,
    count7, TestFreq7,stdv7,
    count8, TestFreq8,stdv8,
    count9, TestFreq9,stdv9,
    count10, TestFreq10,stdv10,
    count11, TestFreq11,stdv11,
    count12, TestFreq12,stdv12
  FROM unpivoted
  PIVOT (
    MAX(ColumnValue) FOR ColumnName IN (
      count1,TestFreq1,stdv1,
      count2,TestFreq2,stdv2,
      count3,TestFreq3,stdv3,
      count4,TestFreq4,stdv4,
      count5,TestFreq5,stdv5,
      count6,TestFreq6,stdv6,
      count7,TestFreq7,stdv7,
    count8, TestFreq8,   stdv8,
    count9, TestFreq9,   stdv9,
    count10, TestFreq10,stdv10,
    count11, TestFreq11,stdv11,
    count12, TestFreq12,stdv12
    )
  ) p
)
select * from pivoted

just to clarify i want to return the same exact results, it's just that for the last column i want to aggregate all values that fall into the 12+ bucket. all the fields are going to be the same except the last three, which are going to be:

+----------+-------------+---------+
| count12+ | TestFreq12+ | stdv12+ |
+----------+-------------+---------+
| 353      | 32423       | NULL    |
| NULL     | NULL        | NULL    |
| 342      | 25324       | NULL    |
+----------+-------------+---------+

please note the much greater numbers above compared to the rest because the 12+ have been aggregated.

thank you so much for your guidance!

Answer 1

It seems that the fastest way to do what you want would be to change your counted2 CTE, so the column TimesTested take your logic into account. So it should be:

counted2 as (
  SELECT
    client_id,
    CASE WHEN TimesTested >= 12 THEN 12 ELSE TimesTested END TimesTested,
    CAST(COUNT(*) AS varchar(30)) AS count,
    CAST(AVG(testfreq) as varchar(30)) as TestFreq,
    CAST(STDEV(TestFreq) as varchar(30)) Stdv
  FROM counted
  GROUP BY
    client_id,
    CASE WHEN TimesTested >= 12 THEN 12 ELSE TimesTested END
    )