CODEWITHSUNDEEP
sqlsql-servermatrix
Adam
Adam

Reputation: 33

How to implement a matrix concept in SQL Server

I am stuck in this concept of creating a matrix in SQL Server where it is created in Excel. I couldn't find good answer online. There are room numbers as the first row and on the first column there are functional requirements. So for example when there is a camera needed in one of the rooms,I will place X mark in the desired row and col coordinate to indicate that it contains one.I attached an sample of the Excel to explain better. Excel Matrix.png

Upvotes: 0

Views: 159

Answers (2)

IronicMuffin
IronicMuffin

Reputation: 4192

Without having data to work with, it's hard to give you an example.

With that said, the pivot method may help you out. You can just have dummy column with a 1 or 0 based on whether or not it has an 'X' in your data. Then in the pivot you would just do a max on that for the various values.

It may require massaging your data into a better format, but should be doable.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152626

Rather than having multiple columns for every possible functional requirement, use proper relational methods for a many-to-may relationship:

 Rooms
 ------
 Id
 RoomName

 Functions
 ---------
 Id
 FunctionName

 RoomFunctions
 -------------
 RoomId
 FunctionId

Then you can relate one room to a variable number of functions, and can add functions easily without changing your data structure.

Upvotes: 2

Related Questions