Simple Split function in SQL Server 2012 with explanation pls

Question

I have two tables Procedures and ProcedureTypes.

Procedures has a column Type which is a varchar with the values (1, 2), (3, 4), (4, 5) etc...

ProcedureType has a primary key 'ID' 1 to 9.

ID   Description
1    Drug
2    Other-Drug
etc...

ID is an integer value and Type is varchar value.

Now I need to join these two tables to show the values

ID in the Procedures table
ProcedureType in the Procedures table
Description in the ProceduresType table with the value separated by a "-".

For example if he value in Type is (1,2) the new table after join should show values in the description like (Drug-Other Drug)

I have used this query bot to no avail

SELECT * FROM dbo.[Split]((select RequestType from GPsProcedures), ',')

Can anyone tell me how to do it and why the above query is not working

Ideally, change the table design. In a sane database, each column ought to contain a single scalar value (per row). Tables are *the* data type designed to hold multiple values (as rows). — Damien_The_Unbeliever, Oct 19 '15 at 10:27
Possible duplicate of [Split function equivalent in T-SQL?](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) — Tab Alleman, Oct 19 '15 at 13:10
SELECT * FROM dbo.[Split]((select RequestType from GPsProcedures ), ',') — Linta Sheelkumar, Oct 19 '15 at 14:03

score 0 · Accepted Answer · answered Oct 19 '15 at 10:57

with Procedures  as (
select 1 as ID, '1,2,3' as Typ
),
ProcedureTypes as (
    select 1 as TypeID, 'Drug' as Name
    union select 2 , 'Other-Drug'
    union select 3 , 'Test 3'
)
/*Get one extra column of type xml*/
,Procedures_xml as (
    select id,CONVERT(xml,' <root> <s>' + REPLACE(Typ,',','</s> <s>') + '</s>   </root> ') as Typ_xml
     from Procedures
)
/*Convert the field string to multiple rows then join to procedure types*/
, Procdure_With_Type as (
select ID,T.c.value('.','varchar(20)') as TypeID,
        ProcedureTypes.Name 
        from Procedures_xml
CROSS APPLY Typ_xml.nodes('/root/s')  T(c) 
INNER JOIN ProcedureTypes ON T.c.value('.','varchar(20)') = ProcedureTypes.TypeID
)
/*Finally, group the procedures type names by procedure id*/
select id,
     STUFF((
    SELECT ', ' + [Name]
    FROM Procdure_With_Type  inn
        WHERE (Procdure_With_Type.ID = inn.ID) 
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
        ,1,2,'') AS NameValues
from Procdure_With_Type
group by ID

This is a good one.but can you tell me why this is not working? SELECT * FROM dbo.[Split]((select RequestType from GPsProcedures ), ',') — Linta Sheelkumar, Oct 19 '15 at 14:10

score 0 · Answer 2 · answered Oct 19 '15 at 15:47

0

You can't have a select statement as a parameter for a function, so instead of this:

SELECT * FROM dbo.[Split]((select RequestType from GPsProcedures), ',')

Use this:

select S.* 
from GPsProcedures P
cross apply dbo.[Split](P.RequestType, ',') S

answered Oct 19 '15 at 15:47

James Z

12,209
10
24
44

Simple Split function in SQL Server 2012 with explanation pls

2 Answers2