CODEWITHSUNDEEP
sqlsql-servert-sql
ChangeTheWay
ChangeTheWay

Reputation: 635

I need a specific output

I have to get a specific output format from my tables.

Let's say I have a simple table with 2 columns name and value.

table T1

+---------------+------------------+
| Name          | Value            |
+---------------+------------------+
| stuff1        | 1                |
| stuff1        | 1                |
| stuff2        | 2                |
| stuff3        | 1                |
| stuff2        | 4                |
| stuff2        | 2                |
| stuff3        | 4                | 
+---------------+------------------+

I know the values are in the interval 1-4. I group it by name and value and count number of the same rows as Number and get the following table:

table T2

+---------------+------------------+--------+
| Name          | Value            | Number |
+---------------+------------------+--------+
| stuff1        | 1                | 2      |
| stuff2        | 2                | 2      |
| stuff3        | 1                | 1      |
| stuff3        | 4                | 1      |
+---------------+------------------+--------+

Here is the part when I need your help! What should I do if I want to get these format?

table T3

+---------------+------------------+--------+
| Name          | Value            | Number |
+---------------+------------------+--------+
| stuff1        | 1                | 2      |
| stuff1        | 2                | 0      |
| stuff1        | 3                | 0      |
| stuff1        | 4                | 0      |
| stuff2        | 1                | 0      |
| stuff2        | 2                | 2      |
| stuff2        | 3                | 0      |
| stuff2        | 4                | 0      |
| stuff3        | 1                | 1      |
| stuff3        | 2                | 0      |
| stuff3        | 3                | 0      |
| stuff3        | 4                | 1      |
+---------------+------------------+--------+

Thanks for any suggestions!

Upvotes: 0

Views: 83

Answers (3)

Karl Kieninger
Karl Kieninger

Reputation: 9129

Variation on the theme.

Differences between Gordon Linoff and Owen existing answers.

  1. I prefer GROUP BY to get the Names rather than a DISTINCT. This may have better performance in a case like this. (See Rob Farley's still relevant article.)
  2. I explode the subqueries into a series of CTEs for clarity.

  3. I use table T2 as the question now labels the group results set instead of showing that as as subquery.

    WITH PossibleValue AS (
  SELECT 1 Value 
   UNION ALL
  SELECT Value + 1 
FROM PossibleValue 
   WHERE Value < 4 
), 
Name AS (
  SELECT Name 
    FROM T1
   GROUP BY Name
),
NameValue AS (
  SELECT Name 
        ,Value
    FROM Name
         CROSS JOIN
         PossibleValue
)
SELECT nv.Name
      ,nv.Value
      ,ISNULL(T2.Number,0) Number
  FROM NameValue nv
       LEFT JOIN
       T2 ON nv.Name = T2.Name
         AND nv.Value = T2.Value

Upvotes: 2

JonPayne
JonPayne

Reputation: 566

Yet another solution, this time using a Table Value Constructor in a CTE to build a table of name value combinations.

WITH value AS 
( SELECT DISTINCT t.name, v.value
    FROM T1 AS t
   CROSS JOIN (VALUES (1),(2),(3),(4)) AS v (value)
)
  SELECT v.name AS 'Name', v.value AS 'Value', COUNT(t.name) AS 'Number'
    FROM value AS v
    LEFT JOIN T1 AS t ON t.value = v.value AND t.name = v.name
GROUP BY v.name, v.value, t.name;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You start with a cross join to generate all possible combinations and then left-join in the results from your existing query:

select n.name, v.value, coalesce(nv.cnt, 0) as "Number"
from (select distinct name from table t) n cross join
     (select distinct value from table t) v left outer join
     (select name, value, count(*) as cnt
      from table t
      group by name, value
     ) nv
     on nv.name = n.name and nv.value = v.value;

Upvotes: 3

Related Questions