Home > Forum > General > How to split Item list colum value to few

How to split Item list colum value to few
0

I have got Item list with 2 columns
1. Category (A)
2. Points (B)
Column A and B use dictionary process where if is set A then filter B to A value

and in column (A) I can pick Value then >> in column (B) points I can pick more than 1 value .
so I can have:
(A) >> (B)
A >> a,b,c,z
B >> a,z,dd,x
C >> a
D >> a,b

and now in sub process

I would like to split this table to:

A >> a
A>> b
A >> c
A>> z
B>> a
B>>z
....
D>>a
D>>b

how to do it? probably is easy like 1-2-3 ...

I tryed generate sql code .. but is not working ...

SELECT
d.DET_WFDID AS 'ID elementu',
d.{DCNCOL_NAME:1174} AS 'Kategoria',
ST.Item AS 'Punkt normy'
FROM
WFElementDetails d
CROSS APPLY
dbo.SplitToTable(CAST(d.{DCNCOL_NAME:1173} AS NVARCHAR(MAX)), ';') AS ST
WHERE
d.DET_WFCONID = {WFCON:3749}
AND d.DET_WFDID = {WFD_WFDID}
AND d.DET_IsDeleted = 0
ORDER BY
d.DET_WFDID;

MVP

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