CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Control Freak
Control Freak

Reputation: 13213

Getting Every Possible Combination Of Columns

If I were to have a recordset that just returned:

SELECT * FROM USERS

    Name | Gender |   Company
    Bob  |  male  |  Abc Inc.
    Bob  |  male  |  Xyz Inc.
    Bob  |  male  |    NULL

And I would like it to return this:

    Name | Gender |   Company
    NULL |  NULL  |    NULL
    NULL |  NULL  |    Abc Inc.
    NULL |  male  |    NULL
    NULL |  male  |    Abc Inc.
    NULL |  NULL  |    Xyz Inc.
    NULL |  male  |    Xyz Inc.
    Bob  |  NULL  |    NULL
    Bob  |  NULL  |    Abc Inc.
    Bob  |  male  |    NULL
    Bob  |  male  |    Abc Inc.
    Bob  |  NULL  |    Xyz Inc.
    Bob  |  male  |    Xyz Inc.

How is it accomplished?

Upvotes: 0

Views: 158

Answers (2)

Voislav Sauca
Voislav Sauca

Reputation: 3075

SELECT DISTINCT NAME, GENDER, COMPANY FROM TABLE
GROUP BY NAME, GENDER, COMPANY WITH CUBE

Upvotes: 0

usr
usr

Reputation: 171178

select Name, Gender, Company
from T
group by Name, Gender, Company with cube

Upvotes: 1

Related Questions