SQL Create unique list of values from table with multiple columns

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
966
Hello,

I am struggling with a SQL statement (im fairly new to it). I have a table that records a seating plan in an office. So each record represents a day, and each column represents a seat. Users then populate the table to book seats on certain days.

PK_DateSeat1Seat2Seat3Seat4Seat5
25/10/2020
26/10/2020HSPH
27/10/2020PHYT
28/10/2020KK
29/10/2020EM

So I wish to return a list

Names
HS
PH
KK
YT
EM

SQL:
SELECT DISTINCT * FROM (
SELECT S1
FROM SeatingT
union
SELECT S2
FROM SeatingT
union
SELECT  S3
FROM SeatingT
union
SELECT  S4
FROM SeatingT
union
SELECT  S5
FROM SeatingT);

I have tried the following, but feel there may be a more elegant solution? Also I need to add a WHERE <> Null clause somewhere as I have a NULL value returned in my table.

Any help is appreciated
Many Thanks
Caleeco
 
Last edited:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
There is a more elegant way, if your data table structure is normalized (which it isn't).

Your data table should be structured with three fields:
- PK Date
- Seat Number
- Seat Value

So, you would never have multiple seats on a single record (you would have multiple records for each day).
If you structure your table like this, your query becomes very simple, as you only have to look at a single column (and not 5).
And it makes it very easy to add more seats in the future (without having to edit your data table structure, and all your queries, forms, reports, etc).

Designing normalized tables is essential in database design. If you do so, most of your tasks can be done without too much trouble.
However, if your tables are not normalized, they can make what should be very simple tasks very complicated and difficult to do.

For example, I once had a table with 32 different mutual fund columns, as companies could have up to 32 different investements. Every now and then, a mutual fund would be discontinued and we would have to find all of them and replace them. Under the original structure, we had to search 32 different columns explicitly! What a nightmare! After normalizing it, it was simply a matter of searching one column.

See here for more details: Database normalization description - Office
 
Solution

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
966
Hi Joe,

Thanks for the response! Ah ok, that makes sense. Thanks for the link I'll read more into normalization.

In reality my actual table has about 40 columns! Like you say, any changes will be a massive headache to implement.

My original solution was in an attempt to visualize the table on floorplan map. So each column, can be linked to a text box (that the user can type their name into to update the back-end table).

I see if I can recreate this using the table structure you have suggested (will need to put my thinking cap on lol).

Cheers
Caleeco
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
I see if I can recreate this using the table structure you have suggested (will need to put my thinking cap on lol).
The nice thing with the normalized structure is that it doesn't matter if there are 5 seats of 40 seats. A table of those 3 fields handles both the same.
This kind of table design allows for this sort of "expansion" without requiring you to have to change the stuctures of your database objects.
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
966
The nice thing with the normalized structure is that it doesn't matter if there are 5 seats of 40 seats. A table of those 3 fields handles both the same.
This kind of table design allows for this sort of "expansion" without requiring you to have to change the stuctures of your database objects.

Yes, that sounds good. I already had to do a lot of work today to add 15 new seats... o_O It was pretty tedious!

Am i right in saying that because this is a booking system. The normalised table will need to have date placeholders (so if I have 5 seats, i will need each date repeated 5 times) so that a user can select a date & Select a Seat & then add their name to the table through a field on a form? :unsure:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
Am i right in saying that because this is a booking system. The normalised table will need to have date placeholders (so if I have 5 seats, i will need each date repeated 5 times) so that a user can select a date & Select a Seat & then add their name to the table through a field on a form? :unsure:
That is one way, though you may also be able to do it on-the-fly with some coding.
A lot of it depends on your needs and how you want to present it to users.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,171
Members
410,775
Latest member
alal1030
Top