Convert a string to a column display

--1. Converting a string to a column displays if OBJECT_ID (' TB ') is not null drop table TB go CREATE TABLE TB ([number] varchar (3), [Product] varchar (2), [qty] int, [Unit Price] int,[amount] int,[serial number] varchar (8)) insert into TB ([number],[product],[quantity],[Unit price],[amount],[Serial number]) SELECT ' 001 ', ' AA ', 3,5,15, ' 12,13,14 ' Union Allselect ' 002 ', ' BB ', 8,9,13, ' 22,23,24 ' Go SELECT [number],[product],[quantity],[Unit price],[amount], substring ([Serial number],b.number,charindex ( ', ', [serial number]+ ', ', B.number]-b.number) as [serial number] from TB A with (NOLOCK), Master: Spt_values B with (NOLOCK) where B.number>=1 and B.number<len (a.[serial number]) and b.type= ' P ' and substring (', ' +[serial number], number,1) = ', ' Go drop table TB go/** number Product quantity Unit price amount serial number-------------------------------  ------------------001 AA 3 5 001 AA 3 5 15 13 001           AA 3 5 002 BB 8 9 002 BB 8 9  002 BB 8 9 */----------

Convert a string to a column display