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.
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.