property.RepoObjectProperty_SelectedPropertyName_split - V
type: V ( view ), modify_date: 2021-10-02 13:23:34
RepoObject_guid: 49EFD408-A51F-EC11-8523-A81E8446D5B0
Description
-
property_value of property.RepoObjectProperty - U is splitted in lines and these lines are splitted in rows
-
Where property_name = 'additional_reference_csv'
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_RepoObjectProperty_SelectedPropertyName_split__1
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
[column-property_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectProperty_SelectedPropertyName_split__2
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
sql_modules_definition
property.RepoObjectProperty_SelectedPropertyName_split - V script
/*
<<property_start>>Description
* property_value of xref:dhw:sqldb:property.repoobjectproperty.adoc[] is splitted in lines and these lines are splitted in rows
* Where property_name = 'additional_reference_csv'
<<property_end>>
*/
CREATE View [property].[RepoObjectProperty_SelectedPropertyName_split]
As
Select
RepoObject_guid
, property_name
, property_value
, value_line = Value2LineSplit.value
, value_line_row = Line2RowSplit.value
, value_line_len = Len ( Value2LineSplit.value )
, LinePerGuidProperty = Dense_Rank () Over ( Partition By
RepoObject_guid
, property_name
Order By
Value2LineSplit.value
)
, RowPerGuidPropertyLine = Row_Number () Over ( Partition By
RepoObject_guid
, property_name
, Value2LineSplit.value
Order By
Value2LineSplit.value
)
From
property.RepoObjectProperty
--String_Split separator must be one (1) char (varchar, nchar, nvarchar)
--we remove Char(13)
--Windows CR LF 13 10
--Unix LF 10
Cross Apply String_Split(property_value, Char ( 10 )) As Value2LineSplit
Cross Apply String_Split(Value2LineSplit.value, ',') As Line2RowSplit
Where
property_name = 'additional_reference_csv'
And Len ( Value2LineSplit.value ) > 1