I'll come with an sql table named VISIT table.

I've 3 posts

WORKSTATION, CATEGORY, BUTTON

1 ,1 , 1

1 ,2 , 3

1 ,1 , 2

2 ,1 , 1

2 ,3 , 1

2 ,1 , 2

How do i query that table to obtain the distinct values for every column in a single query statement?

the end result ought to be sth like..

WORKSTATION, CATEGORY, BUTTON

1 ,1 , 1

2 ,2 , 2

  • ,3 , 3

I'm able to do this with 3 different queries. I simply question is this completed in one query as that can make this program improve your speed.

Trying to get this done with column-per-column is IMO an error simply do it in 2 posts and choose the column in the caller:

SELECT DISTINCT 'WS' AS [Col], WORKSTATION AS [Value] FROM VISIT
UNION ALL
SELECT DISTINCT 'CA' AS [Col], CATEGORY AS [Value]  FROM VISIT
UNION ALL
SELECT DISTINCT 'BU' AS [Col], BUTTON AS [Value]  FROM VISIT

that is more-or-less just like:

SELECT 'WS' AS [Col], WORKSTATION AS [Value] FROM VISIT
UNION
SELECT 'CA' AS [Col], CATEGORY AS [Value]  FROM VISIT
UNION
SELECT 'BU' AS [Col], BUTTON AS [Value]  FROM VISIT

Your particulars are often one round-trip, etc. I'd most likely make use of an integer (possibly even tinyint) for that first column (using the client likely to realize that 1 maps to WORKSTATION etc) as opposed to a varchar, however the above is offered for flavor only.


Edit re comments the next considers:

  • including different data types within the union (transforming to some common type)
  • using ordinal sorts around the original data, so integers continue to be sorted 1,2,10 (integer sort), not 1,10,2 (string-sort)

TSQL:

DECLARE @VISIT TABLE (WORKSTATION int, CATEGORY varchar(10), BUTTON int)
INSERT @VISIT VALUES (1,'1',1)
INSERT @VISIT VALUES (1,'2',3)
INSERT @VISIT VALUES (2,'1',2)
INSERT @VISIT VALUES (10,'1',1)
INSERT @VISIT VALUES (10,'3',1)
INSERT @VISIT VALUES (10,'1',2)

SELECT x.Col, x.Value
FROM (
  SELECT CAST(1 as tinyint) AS [Col], ROW_NUMBER() over(order by WORKSTATION)
    AS [Sort], CONVERT(varchar(20), WORKSTATION) AS [Value] FROM @VISIT
  UNION
  SELECT CAST(2 as tinyint) AS [Col], ROW_NUMBER() over(order by CATEGORY)
    AS [Sort], CATEGORY AS [Value] FROM @VISIT
  UNION
  SELECT CAST(3 as tinyint) AS [Col], ROW_NUMBER() over (order by BUTTON)
    AS [Sort], CONVERT(varchar(20), BUTTON) AS [Value] FROM @VISIT
) x ORDER BY x.Col, x.Sort

A few ideas...

Two Full Outer Joins:

SELECT DISTINCT w.workstation, c.category, b.button
  FROM VISIT w FULL OUTER JOIN VISIT c ON (w.workstation = c.category)
  FULL OUTER JOIN VISIT b ON (w.workstation = b.button OR c.category = b.button)

Subqueries for VISIT may quicken things too:

SELECT w.workstation, c.category, b.button
FROM (SELECT DISTINCT workstation FROM VISIT) w 
FULL OUTER JOIN (SELECT DISTINCT category FROM VISIT) c 
             ON (w.workstation = c.category)
FULL OUTER JOIN (SELECT DISTINCT button FROM VISIT) b 
             ON (w.workstation = b.button OR c.category = b.button)

Purchased if you want:

ORDER BY COALESCE(w.workstation, c.category, b.button)