SQL DISTINCT Value

Transact-SQL
1

I have a table with 3 columns and some rows.
I want to write a query that gives me distinct value for each column (each column shows its own distinct values) does not depend on other columns.

CREATE TABLE mytable(
Col_A VARCHAR(1) NOT NULL
,Col_B INTEGER NOT NULL
,Col_C INTEGER NOT NULL
);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('A',10,623);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('A',20,654);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('A',30,869);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('B',40,362);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('B',50,623);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('B',60,654);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('C',10,869);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('C',20,362);
INSERT INTO mytable(Col_A,Col_B,Col_C) VALUES ('C',30,623);

Expected Result

image

2

Hi

I have taken it till here
Maybe someone else can help
Thanks

++++++++++++++++++++++++++++++++++++
create data script

Summary 
DROP TABLE harish_mytable;

CREATE TABLE harish_mytable 
  ( 
     col_a VARCHAR(1) NOT NULL, 
     col_b INTEGER NOT NULL, 
     col_c INTEGER NOT NULL 
  ); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('A', 
             10, 
             623); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('A', 
             20, 
             654); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('A', 
             30, 
             869); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('B', 
             40, 
             362); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('B', 
             50, 
             623); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('B', 
             60, 
             654); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('C', 
             10, 
             869); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('C', 
             20, 
             362); 

INSERT INTO harish_mytable 
            (col_a, 
             col_b, 
             col_c) 
VALUES      ('C', 
             30, 
             623); 

SELECT * FROM harish_mytable;

++++++++++++++++++++++++++++++++++++
Query Data Script

Summary 
;WITH abc_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY a.col_a) row_num, 
                a.col_a 
         FROM   (SELECT DISTINCT col_a 
                 FROM   harish_mytable) a), 
     def_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY a.col_b) row_num, 
                a.col_b 
         FROM   (SELECT DISTINCT col_b 
                 FROM   harish_mytable) a), 
     ghi_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY a.col_c) row_num, 
                a.col_c 
         FROM   (SELECT DISTINCT col_c 
                 FROM   harish_mytable) a) 
SELECT col_a, 
       col_b, 
       col_c 
FROM   abc_cte a 
       FULL OUTER JOIN def_cte b 
                    ON a.row_num = b.row_num 
       FULL OUTER JOIN ghi_cte c 
                    ON a.row_num = c.row_num

++++++++++++++++++++++++++++++++++++
Result

Summary

image

3 
SELECT
    Col_A,
    Col_B,
    Col_C
FROM (
    SELECT Col_A, ROW_NUMBER() OVER(ORDER BY Col_A) AS row_num
    FROM (
        SELECT DISTINCT Col_A
        FROM dbo.mytable
    ) AS Col_As
) AS Col01
FULL OUTER JOIN (
    SELECT Col_B, ROW_NUMBER() OVER(ORDER BY Col_B) AS row_num
    FROM (
        SELECT DISTINCT Col_B
        FROM dbo.mytable
    ) AS Col_Bs
) AS Col02 ON Col02.row_num = Col01.row_num
FULL OUTER JOIN (
    SELECT Col_C, ROW_NUMBER() OVER(ORDER BY Col_C) AS row_num
    FROM (
        SELECT DISTINCT Col_C
        FROM dbo.mytable
    ) AS Col_Cs
) AS Col03 ON Col03.row_num IN (Col01.row_num, Col02.row_num)
1 Like
4

Many Thanks :slight_smile: