CODEWITHSUNDEEP
sqlsql-servert-sql
Rod
Rod

Reputation: 15433

trying to get data to be chart ready

Given: SqlFiddle Demo

TopProducts table
------------------
Name, Count, Choice
WidgetA, 3, Pick1
WidgetB, 1, Pick1
WidgetB, 1, Pick2
WidgetC, 1, Pick2
WidgetA, 1, Pick3
WidgetD, 3, Pick3

I'd like to get the data ready for charting if possible. The chart will have multiple series based on choices. I imagine the data should look like the following:

WidgetA, 3, Pick1
WidgetB, 1, Pick1
WidgetC, null, Pick1
WidgetD, null, Pick1
WidgetA, null, Pick2
WidgetB, 1, Pick2
WidgetC, 1, Pick2
WidgetD, null, Pick2
WidgetA, 1, Pick3
WidgetB, null, Pick3
WidgetC, null, Pick3
WidgetD, 3, Pick3

Upvotes: 1

Views: 47

Answers (1)

John Woo
John Woo

Reputation: 263703

You need to get the cartesian product of unique values on columns: Name and Choice to be able to get all the possible combinations.

The result of the cartesian product will then be joined using LEFT JOIN by table to get the value of the Count.

SELECT  DISTINCT
        b.Name,
        c.Count,
        a.Choice
FROM    (SELECT DISTINCT Choice FROM TopProducts) a
        CROSS JOIN (SELECT DISTINCT Name FROM TopProducts) b
        LEFT JOIN TopProducts c
            ON  a.Choice = c.Choice
                AND b.name = c.Name
ORDER   BY a.Choice, b.Name

OUTPUT

╔═════════╦════════╦════════╗
║  NAME   ║ COUNT  ║ CHOICE ║
╠═════════╬════════╬════════╣
║ WidgetA ║ 3      ║ Pick1  ║
║ WidgetB ║ 1      ║ Pick1  ║
║ WidgetC ║ (null) ║ Pick1  ║
║ WidgetD ║ (null) ║ Pick1  ║
║ WidgetA ║ (null) ║ Pick2  ║
║ WidgetB ║ 1      ║ Pick2  ║
║ WidgetC ║ 1      ║ Pick2  ║
║ WidgetD ║ (null) ║ Pick2  ║
║ WidgetA ║ 1      ║ Pick3  ║
║ WidgetB ║ (null) ║ Pick3  ║
║ WidgetC ║ (null) ║ Pick3  ║
║ WidgetD ║ 3      ║ Pick3  ║
╚═════════╩════════╩════════╝

Upvotes: 1

Related Questions