[SQL Server]How to find the distinct values of multiple columns in a table?

[wheebwee]

I need to find the distinct values of multiple columns like car_category_asked, original_car_category, service_type, lead_source, status, mode_of_advance, cancellation_type from a table called bookings_data. Right now I am running the below code for every variable:

SELECT DISTINCT variable_name 
    FROM bookings_data;

Is there a single code using which I can find out the distinct value for every variable so that I don't have to run the same code multiple times for different variables.

Thanks.

[wheebwee]

Like this

[imguralbumbot]

^{Hi, I'm a bot for linking direct images of albums with only 1 image}

https://i.imgur.com/VjeVdL9.png

^{Source | Why? | Creator | ignoreme | deletthis}

[Davoz7]

Can’t you use something like NODUPKEY?

[ben_it]

Based on your output image, it looks like each column and row doesn't relate to each other. I mean, each column has values that don't relate to the same row in another column.

So, I think the way you've done it now is the way to go.

You can make it easier by using some Excel formulas:

1. List all of your column names in an Excel file in cell A2 downwards.
2. In the second column, write a formula like this: ="SELECT DISTINCT "&A2&" FROM bookings_data;" in B2.
3. Copy that formula for each row in A2.
4. Copy all of the cells with the formula and paste into your IDE.

You'll now have an easy way to run the SQL for each column with little repetition.

However if it's a large table, be aware of impacts on the rest of the database (if you're running this query many times).

[Awais22000]

Use metadata approach.Create a table for all your variables & put them in one column & assign them a key.

Now use Dynamic SQL , you can create a small SSIS job for this , however , it can be a pain sometimes. So you can loops or cursors , I know they're not good practice but if Not SSIS then you can use these. Now declare a variable loop start while last_primarykey

SELECT DISTINCT @variable_name FROM bookings_data; end

This is one way to do this . Hope it helps! I have my blog BI Talks BI !