Post reply

Get Distinct Values from 3 Cloumns

  • May 13, 2009 at 10:33 am

    #204787

    Hi All

    I am having a table that has got 2 million rows with 12 columns, I need to create a separate table with the same structure, but i need to fill 3 columns with distinct values from the master table and leave the others as null.

    I have now created a table and inserting rows one by one by selecting a distinct values from the master table, is there any other ways to do this in a go,

    Create table #TempA ( Name Varchar(20), Age Int,Hobby Varchar(20),HobbyCode Int)

    Insert into #TempA Values ('Test1',20,'FootBall',1)

    Insert into #TempA Values ('Test2',22,'BaseBall',2)

    Insert into #TempA Values ('Test3',20,'Bird',4)

    Insert into #TempA Values ('Test4',24,'SQL',5)

    Insert into #TempA Values ('Test5',20,'Book',5)

    Create table #TempB ( Name Varchar(20), Age Int,Hobby Varchar(20),HobbyCode Int)

    The Output of #TempB should be all Null on Name, Distinct Values of Age from TableA, Null on Hobby, Distinct Values on HobbyCode from TableA

    For now i create table b and then insert one by one column by select distinct number from tableA.

    Is there any other way of doing this.

    Thanks in Advance for all your Help 🙂

  • May 13, 2009 at 10:56 am

    #993904

    insert TableB select select distinct null,age,null, hobbycode from TableA

  • May 13, 2009 at 10:59 am

    #993908

    Thanks mate, But if i use this then i will get duplicate values on columns, i Looking to have Distinct values in Each column. 🙂

  • May 13, 2009 at 11:23 am

    #993932

    Hi CrazyMan,

    I think that the solution provided by FelixG is the one because you'll have the distinct combination.

    Based on the example you provide, you have 3 different values in Age column and 4 different values on HobbyCode, so if you really want distinct values on all the values of the column, you'll have to fill with a null on age.

    But, what if you have 3 different values on Age and 5 on HobbyCode ?? Will you use 2 null in Age ??

    cheers

    Alejandro Pelc

  • May 13, 2009 at 11:46 am

    #993945

    I did not understand the original question.... here goes another solution... hope this helps

    select distinct id= identity (int,1,1), age into #c from #tempa

    select distinct id= identity (int,1,1), hobbycode into #d from #tempa

    select age,hobbycode from #c

    full outer join #d

    on #c.id=#d.id

  • May 14, 2009 at 2:49 am

    #994327

    Thanks Flexi & Alejandro

    AS Ale said , i am filling up the remaining fields with NULL Values, Currently i am having the result which i need in a table with Null values as below

    Insert into #TempB (Age)

    Select Distinct Age from #TempA

    Insert into #TempB (HobbyCode)

    Select Distinct HobbyCode from #TempA

    Select * from #TempB

    When you run the above 2 insert statements, The result will have null values on Name and Hobby , which is fine, but When you see Hobby code table, Insert starts from Row 4, not from Row1, Is this the indented behavior of SQL, Can i insert from Row1.

    That was my initial Question, Sorry for Confusing you guys, your help is much appreciated 🙂

    Cheers

  • May 14, 2009 at 7:24 am

    #994491

    I just added an identity column to both auxiliary tables and then full joined then just to ensure that both inserts will start in the first row... try it and you'll see....

  • May 14, 2009 at 7:36 am

    #994507

    Create table #TempA ( Name Varchar(20), Age Int,Hobby Varchar(20),HobbyCode Int)

    maybe I'm reading the requirements wrong, but if you want the current distinct combinations that exist in the table, would it not be

    SELECT DISTINCT Age ,Hobby,HobbyCode FROM #TempA

    and if you wanted all possible values, would it not be a three way cross join?

    SELECT AGE,NULL NULL FROM #TempA A1

    CROSS JOIN

    (SELECT NULL,Hobby,NULL FROM #TempA) A2

    CROSS JOIN

    (SELECT NULL NULL,HobbyCode FROM #TempA) A3

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 15, 2009 at 8:18 am

    #995374

    Thanks Guys

    That Worked, Thanks for your help 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply