sqlsql-serverdistinct

SQL create table from a select distinct with conditions


I have a table, lets call it table1 which has many columns, of this table I want to now create a new table which contains unique values of one column of table1 grouped or with unique values of another column if in a third column teh value is equal to "staff"

column1 | column2 | column3 | ....
  john  |   store |  staff  
  luis  |   front |  staff
  carlos|   store |  temp 
  luis  |   front |  staff

what I want would be a table like this:

 user | dept 
 john | store
 luis | front
 john | front

my first attempt:

create table users
as 
   select distinct column1,column2
   from table1
   where column3 ='staff'

this however is not a correct syntax , it says "incorrect syntax near the keyword select.

so my second attempt was this : create table as

   Select Distinct column1, from table1 group by column2 where column3 = 'staff'

However this attempt gives me thee same error. My SQL syntax knowledge is not that good as you can see so I wanted to know first if it was possible to do something like this, and second what would be the correct syntax. This is so I can create a index table that I can use instead of having to create this in power bi.


Solution

  • You can create a table based on your SELECT statement.

    select distinct column1, column2
    into table2
    from table1
    where column3 ='staff'
    

    This statement above will create a new table with the name table2 based on the columns in SELECT and their data types. In this case table2 will have two columns with names column1 and column2.