I want to get distinct of two columns from an SQL table. Can I optimize this query?

create TABLE #Temporary_tbl
    ProductColour VARCHAR(50),
    ProductSize VARCHAR(20),

insert into #Temporary_tbl (ProductColour)
select distinct productcolour
from shoptransfer

insert into #Temporary_tbl (ProductSize)
select distinct ProductSize
from shoptransfer

select * from #Temporary_tbl

This will give you the same result as your sql.

select distinct ProductColour, null as ProductSize
from shoptransfer

union all

select distinct null as ProductColor, ProductSize
from shoptransfer

You don't actually need the "as" clause on the second select, but I find it good for readability.

Ultimatly, sql server will mostly be doing the same thing, but by removing the explicit temporary table and putting it all into one command, you give sql server a better chance of doing the two selects in parallel.

  • 1
    \$\begingroup\$ Can i union them without null values. \$\endgroup\$
    – Zain
    Apr 7 '11 at 9:31
  • \$\begingroup\$ The number of columns needs to line up, if the nulls were removed then all the values would be placed in a single column, which would be a change in behavior from what you provided as your starting point. \$\endgroup\$ Apr 7 '11 at 9:39
  • \$\begingroup\$ But i want them in two column with out null is this possible. \$\endgroup\$
    – Zain
    Apr 7 '11 at 10:11
  • \$\begingroup\$ If both columns from the source table have the same number of distinct values (or if it's distinct pairs you want) then I'm sure you can. The details of which depend largely on exactly what it is you want. \$\endgroup\$ Apr 7 '11 at 10:49
  • \$\begingroup\$ Shouldn't the first NULL be converted explicitly to varchar(20)? \$\endgroup\$
    – Andriy M
    May 5 '11 at 5:57
  CASE x.f WHEN 1 THEN s.ProductColour END AS ProductColour
  CASE x.f WHEN 2 THEN s.ProductSize   END AS ProductSize
FROM shoptransfer s

This produces the same output as Brian's solution. The idea is to make every row of shoptransfer output twice without scanning the table twice. That is achieved by cross-joining the table to a tiny tally table created 'on the fly'. When the tally table's first row is current, the query produces a row with a ProductColour value and NULL as ProductSize, and the second time it's the other way round, i.e. ProductColour is NULL and ProductSize contains a value from the shoptransfer table.

The syntax used for defining the tally table may seem a bit unusual. For those of you not acquainted with that way of aliasing, x is the subselect's alias, the f in brackets is the alias for the subselect's single column. In short, the following two definitions are absolutely equivalent to each other:

  1. (SELECT 1 UNION ALL SELECT 2) x (f)


  • 1
    \$\begingroup\$ Good idea. A quick test shows your approach runs in about half the time of mine, though it took me a moment to see what you were doing (I have never seen that x (f) notation before). You might want to add a short comment to your answer explaining the logic behind it. \$\endgroup\$ Jun 7 '11 at 8:57

No temporary table should be necessary.

SELECT DISTINCT productcolour FROM shoptransfer
SELECT DISTINCT ProductSize FROM shoptransfer;

Furthermore, it would be a good idea to be more consistent with capitalization, even though SQL Server is not case sensitive.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.