Is there a way you can produce an output like this in T-SQL-CodePudding

I have a column which I translate the values using a case statements and I get numbers like this below. There are multiple columns I need to produce the result like this and this is just one column.

How do you produce the output as a whole like this below. The 12 is the total numbers counting from top to bottom 49 is the Average. 4.08 is the division 49/12. 1 is how many 1's are there in the output list above. As you can see there is only one 1 in the output above 8.33% is the division and percentage comes from 1/12 * 100 and so on. Is there a way to produce this output below?

drop table test111
create table test111
(
Q1 nvarchar(max)
);

INSERT INTO TEST111(Q1)
VALUES('Strongly Agree')
,('Agree')
,('Disagree')
,('Strongly Disagree')
,('Strongly Agree')
,('Agree')
,('Disagree')
,('Neutral');

SELECT
CASE WHEN [Q1] = 'Strongly Agree' THEN 5
WHEN [Q1] = 'Agree' THEN 4
WHEN [Q1] = 'Neutral' THEN 3
WHEN [Q1] = 'Disagree' THEN 2
WHEN [Q1] = 'Strongly Disagree' THEN 1
END AS 'Test Q1'
FROM test111

CodePudding user response：

I have to make a few assumptions here, but it looks like you want to treat an output column like a column in a spreadsheet. You have 12 numbers. You then have a blank "separator" row. Then a row with the number 12 (which is the count of how many numbers you have). Then a row with the number 49, which is the sum of those 12 numbers. Then the 4.08 row, which is rougly the average, and so on.

Some of these outputs can be provided by cube or rollup, but neither is a complete solution.

If you wanted to produce this output directly from TSQL, you would need to have multiple select statements and combine the results of all of those statements using union all. First you would have a select just to get the numbers. Then you would have a second select which outputs a "blank". Then another select which is providing a count. Then another select which is providing a sum. And so on.

You would also no longer be able to output actual numbers, since a "blank" is not a number. Visually it's best represented as an empty string. But now your output column has to be of datatype char or varchar.

You also have to make sure rows come out in the correct order for presentation. So you need a column to order by. You would have to add some kind of ordering column "manually" to each of the select statements, so when you union them all together you can tell SQL in what order the output should be provided.

So the answer to "can it be done?" is technically "yes". But if you think seems like a whole lot of laborious and inefficient TSQL work, you'd be right.

The real solution here is to change your approach. SQL should not be concerned with "output formatting". What you should do is just return the actual data (your 12 numbers) from SQL, and then do all of the additional presentation (like adding a blank row, adding a count row, etc), in the code of the program that is calling SQL to get that data.

CodePudding user response：

I must say, this is one of the strangest T-SQL requirements I've seen, and is really best left to the presentation layer.

It is possible using GROUPING SETS though. Once you have the rollup, you need to unpivot the totalled row (identified by GROUPING() = 1) to get your final result.

This is impossible without a row-identifier. I have added ROW_NUMBER, but any primary or unique key will do.

WITH YourTable AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn,
CASE WHEN [Q1] = 'Strongly Agree' THEN 5
WHEN [Q1] = 'Agree' THEN 4
WHEN [Q1] = 'Neutral' THEN 3
WHEN [Q1] = 'Disagree' THEN 2
WHEN [Q1] = 'Strongly Disagree' THEN 1
END AS TestQ1
FROM test111
),
RolledUp AS (
    SELECT
      rn,
      TestQ1,
      grouping = GROUPING(TestQ1),
      count = COUNT(*),
      sum = SUM(TestQ1),
      avg = AVG(TestQ1 * 1.0),
      one = COUNT(CASE WHEN TestQ1 = 1 THEN 1 END),
      onePct = COUNT(CASE WHEN TestQ1 = 1 THEN 1 END) * 1.0 / COUNT(*)
    FROM YourTable
    GROUP BY GROUPING SETS(
        (rn, TestQ1),
        ()
    )
)
SELECT v.TestQ1
FROM RolledUp r
CROSS APPLY (
    SELECT r.TestQ1, 0 AS ordering
    WHERE r.grouping = 0
    UNION ALL
    SELECT v.value, v.ordering
    FROM (VALUES
        (NULL    , 1),
        (r.count , 2),
        (r.sum   , 3),
        (r.avg   , 4),
        (r.one   , 5),
        (r.onePct, 6)
    ) v(value, ordering)
    WHERE r.grouping = 1
) v
ORDER BY
  v.ordering,
  r.rn;

db<>fiddle