November 5, 2019 at 9:02 pm
Hi all T-SQL Masters,
I've actually a challenge. I've three tables defined like this
T_ATTRIBUTES (id_attribute,attribute_name)
VALUES (HAVE_COUCH, HAVE_LAMP, HAVE_BED, etc...)
T_ROOMS (id_room, room_number, owner name)
T_ROOM_ATTRIBUTES (id_room_attribute,id_ref_room,id_ref_attribute,value)
I'm sure you're already understand where I'm going with this.
So I can ADD any ROOM Attribute, Set Any valid attributes value to the correct attribute value, but when I do a simple select, I need to have only one ROW with the ATTRIBUTE_NAME as a Column, not a row like this.
SELECT * FROM view_rooms WHERE id_room = 1;
--I want that
id_room, room_number, owner name, HAVE_COUCH, HAVE_LAMP, HAVE_BED
1,my room,me,1,1,0
-- I dont want that
1,My room,me,1
1,My room,me,1
1,My room,me,0
I definitely dont know how to start. I've try with a table_function, view, nothing work. Try a kind of "FOREACH" approach, WHILE, I dont know. I've read on the PIVOT function, "head bang"
Any info to help me?
Thank you.
November 5, 2019 at 9:52 pm
The PIVOT function will do what you want. You can also google for the old school way of doing a pivot. It's called a "Crosstab".
I find the syntax easier to read than PIVOT. See the example code below.
At heart the crosstab is a combination of a GROUP BY clause and aggregates of CASE expressions for each column. It produces the same results as a pivot and produces a similar execution plan.
But I feel I must caution you against your plan to use rows instead of columns. Over the years, many newcomers have tried this approach to create a more "object-oriented" database. This design causes performance to suffer and ultimately it will not scale well. My final recommendation would be to bite the bullet and add columns. Good luck.
select StoreID
, max(case when CriteriaNo = 9 then Result end) as CriteriaNo9
, max(case when CriteriaNo = 9 then Position end) as CriteriaPosition9
, max(case when CriteriaNo = 10 then Result end) as CriteriaNo10
, max(case when CriteriaNo = 10 then Position end) as CriteriaPosition10
, sum(Result) as PerformancePoint
from SomeTable
group by StoreID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 5, 2019 at 9:53 pm
Is the value column in the T_ROOM_ATTRIBUTES table necessary? Why not delete row(s) (in T_ROOM_ATTRIBUTES) if a room no longer has attribute(s)?
Do you want the target output to always have a column value for every row in T_ATTRIBUTES?
The attributes for each room could be serialized into a piece of Json. Would that fit your purpose?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 6, 2019 at 12:55 pm
Hi scdecade,
Do you want the target output to always have a column value for every row in T_ATTRIBUTES?
Yes, this is the researched goal. I successfully realize a PIVOT with fixed values. I found a website explaning what I try to realize but, it's a nightmare.
https://blog.devoworx.net/2014/04/18/dynamic-pivot-with-out-aggregation-function/
I'm sure that we can have another way to realize that.
Any help will be appreciated.
Thank you
November 6, 2019 at 1:07 pm
HI The Dixie Flatline,
Thank you for your reply. I definitely understand and agree with you when you say:
But I feel I must caution you against your plan to use rows instead of columns. Over the years, many newcomers have tried this approach to create a more "object-oriented" database. This design causes performance to suffer and ultimately it will not scale well. My final recommendation would be to bite the bullet and add columns.
The problem is that the requested parameters are dynamic over the time. And honestly, I can't imagine, in 2019, a DB design requiring view, SP and table update when someone want to add, dynamicaly, an attribute/parameter to a row. More, when I update a parameter and need an historical values for the parameters, what is your suggestion?
Thank you for your time and suggestions
Martin
November 6, 2019 at 2:35 pm
The problem is that the requested parameters are dynamic over the time. And honestly, I can't imagine, in 2019, a DB design requiring view, SP and table update when someone want to add, dynamically, an attribute/parameter to a row.
I think The Dixie Flatline was suggesting you drop both the T_ATTRIBUTES table and T_ROOM_ATTRIBUTES table. Then add attribute columns to the T_ROOMS table.
create table T_ROOMS(
id_roomint identity(1,1) constraint pk_t_rooms primary key not null,
room_numberint unique not null,
owner_namenvarchar(96) not null,
have_couchbit not null default 0,
have_lampbit not null default 0,
have_bedbit not null default 0);
go
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 7, 2019 at 8:43 am
Hi guy's,
Well, I've try another approach. I've delete all my attributes tables and apply the suggestion to have real column and it's really better. To have the dynamic fields required, I've use the system table and search, each time, my table column having, in the description, the value "visible".
SELECT
COLUMN_NAME as column_name,
value as field_parameter
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
sys.extended_properties s
ON
s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.minor_id = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
AND i_s.TABLE_NAME = 't_rooms'
AND CAST(value as varchar(1024)) LIKE 'visible%'
ORDER BY
i_s.COLUMN_NAME
That's work :). I can add/removes dynamicaly parameters and send to my GUI the required fields on the fly.
Ok, each time I've to update the attached view, but in the context, this is not a big deal. Thank you for your reflexions.
November 7, 2019 at 7:46 pm
Besides assigning items like couch, lamps, bed to rooms, ... this UI is a tool to enforce which columns other UI's or database clients can see? So maybe it makes sense to reconsider the recommendation? If you'd like the list of visible columns to apply to every request it could be placed as an attribute in the (old) T_ATTRIBUTES table. Basically, your old design except the visible [value] would move from the cross reference table T_ROOM_ATTRIBUTES to the T_ATTRIBUTES table. Does this make sense? If so, you could oltp insert/delete rows from T_ROOM_ATTRIBUTES to account for changes in room items. You could oltp insert/delete/update rows in T_ATTRIBUTES to alter which columns are visible to all clients.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply