split column into new row for three column

Question

I tried to using ;with tmp() and union all but still no luck via mssql.

This is my source

instance  |host   |servicename
DB,tomcat |abc,xyz|sap,java
null      |efg,ijn|D40,L90

And I would like to get the output like this

instance|host|servicename
DB      |abc |sap
Tomcat  |xyz |java
        |efg |D40
        |ijn |L90

Do you have any solution how to maka a relationize for this?

Is it always 2 or nothing? I mean, can you have a row with `abc,dei|a|b,c,d,e|`? — Zohar Peled, Nov 05 '18 at 10:08
*"I tried to using `;with tmp()` and `union all`"* show us that attempt then. *(Also, `;` is a terminator, not a "beginninator". You should be terminating your statements with a `;`, not just starting statements with a `;` that require the **previous** statement to be terminated.)* — Thom A, Nov 05 '18 at 10:22
https://stackoverflow.com/questions/10914576/t-sql-split-string — Salman A, Nov 05 '18 at 10:24
@Sreenu131it's not good courtesy to demand a user to accept your answer as the accepted answer. — Thom A, Nov 05 '18 at 10:34
@Rindu Saving the data in comma separated form is a bad design — Sreenu131, Nov 05 '18 at 10:34
@Sreenu131 what if your answer is wrong? (I'm not saying it is, but it might be) — Zohar Peled, Nov 05 '18 at 11:36
@ZoharPeled i am just trying to help him in that process i asked him to accept it.i didn't think the great experts like you ask me this question.its just matter of comment and its his choice — Sreenu131, Nov 05 '18 at 11:54
@Sreenu131 I'm far from being a great expert, however I do have some experience with SQL Server, and more importantly, with stackoverflow. Answering incomplete questions is one thing, expecting the OP to accept your answer while it was based (at least partially) on assumptions you made that are not necessarily correct - that's a different thing. IMHO, this question is not yet ready to receive an answer, let alone an answer that can be confidently trusted as solving the issue at hand. Too many details are missing in the question itself. — Zohar Peled, Nov 05 '18 at 12:00

score 1 · Accepted Answer · answered Nov 05 '18 at 10:29

Try this

IF OBJECT_ID('tempdb..#Temp')IS NOT NULL
DROP TABLE #Temp
;WITH CTE(instance  ,host   ,servicename)
AS
(
SELECT 'DB,tomcat' ,'abc,xyz','sap,java' UNION ALL
SELECT null      ,'efg,ijn','D40,L90'
)

SELECT ROW_NUMBER()OVER(ORDER BY instance) AS Seq
        ,ISNULL(instance,'NA') AS instance
        ,Host
        ,servicename
 INTO #Temp FROM CTE

SELECT a.Seq
        ,Split.a.value('.','nvarchar(1000)') AS instance
        ,Split1.a.value('.','nvarchar(1000)') AS host
        ,Split2.a.value('.','nvarchar(1000)') AS servicename
FROM(
SELECT Seq
    ,CAST('<S>'+REPLACE(instance,',','</S><S>')+'</S>'  AS XML )AS instance
    ,CAST('<S>'+REPLACE(host,',','</S><S>')+'</S>'  AS XML )AS host
    ,CAST('<S>'+REPLACE(servicename,',','</S><S>')+'</S>'  AS XML )AS servicename
    FROM #Temp 
) As a
CROSS APPLY instance.nodes('S') AS Split(a)
CROSS APPLY host.nodes('S') AS Split1(a)
CROSS APPLY servicename.nodes('S') AS Split2(a)
ORDER BY seq

split column into new row for three column

1 Answers1