[solved] How to split Item list colum value to few

0

Hi

DECLARE @tmp TABLE
(
col1 VARCHAR(2),
col2 VARCHAR(50),
wfd_ID int
)

INSERT INTO @tmp
SELECT 'A' AS col1, 'a;b;c;z' AS col2, '123' as wfd_ID
UNION ALL
SELECT 'B' AS col1, 'a;z;dd;x' AS col2, '123' as wfd_ID

select * from @tmp


SELECT
t.wfd_ID,
t.col1,
--value AS col2
dd.item AS col2
FROM
@tmp t
CROSS APPLY
--STRING_SPLIT(t.col2, ';');
dbo.splittotable(t.col2, ';') as dd


You can use STRING_SPLIT instead of dbo.splittotable


Regards

Ok I see where you have a mistake, don't use the alias d. for a column in split to table because you get this result for the generated code
cast(d.dbo.ClearWFElemAdv(DET_

you can do it like this : dbo.SplitToTable(CAST(dbo.ClearWFElemAdv(d.{DCNCOL:1173}) AS NVARCHAR(MAX)), ';') AS ST

Hi,
thank you for help and direction.

I don't know why, but "STRING_SPLIT" is not working ...
(did not recognize function ect. .. with declare I have got the same problem ... - maybe not set proper rights in database .. I don't know)

but dbo.SplitToTable works :D

any way in my scenario the proper code is:

[code="SQL"]
SELECT
a.DET_WFDID AS 'ID elementu',
{DCNCOL_NAME:1174} AS 'Kategoria',
ST.Item AS 'Punkt pytania'
FROM
WFElementDetails a
CROSS APPLY
dbo.SplitToTable(CAST({DCNCOL:1173} AS NVARCHAR(MAX)), ';') AS ST
WHERE
a.DET_WFDID = {WFD_WFDID}
and a.DET_WFCONID={WFCON:3749}
AND a.DET_IsDeleted = 0 -- Opcjonalnie, aby pominąć usunięte wiersze

[/Code]

and for Attribute in form (not Item list):

[code="SQL"]
SELECT
a.WFD_ID AS 'ID elementu',
ST.Item AS 'Punkt normy'
FROM
WFElements a
CROSS APPLY
dbo.SplitToTable(CAST('{3654}' AS NVARCHAR(MAX)), ';') AS ST
WHERE
a.WFD_ID = {WFD_ID}

[/Code]

The STRING_SPLIT function is available in Microsoft SQL Server starting with SQL Server 2016.
See what version you have :)

The other issue is it seems to me that this condition is redundant
AND a.DET_IsDeleted = 0 -- Optionally, to skip deleted rows
In the table, this column always has a value of 0 , and the rows are simply deleted and not marked as inactive. The column is probably some kind of historical past.

Check if you have any entries that are not 0 in the DET_IsDeleted column :)
select DET_IsDeleted, * from dbo.WFElementDetails
where isnull(DET_IsDeleted,0) <> 0

Regards.

I did - result = zero ...

I removed "AND a.DET_IsDeleted = 0 -- Optionally, to skip deleted rows"

thank you.