show one column in multiple rows

Question

I’ve been looking for a way to show one column in multiple rows, one cell. The content of it separated by comma’s.

For example, in stead of:

ProjectID Label ———— ——– 1200 label1 1200 label2 1200 label3

I would like the result of my query to look like this:

ProjectID                    Label
————                   ——–
1200                          label1, label2, label3

thanks in advance

check this one http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Andrey Gurinov, Mar 14 '12 at 12:34

Lamak · Accepted Answer · 2012-03-14T13:32:22.053

There are different ways to do this. One option is to create a table valued function that 'splits' your multiple valued cell on different records. Here is an example of an split function:

ALTER FUNCTION [dbo].[Split](@RowData VARCHAR(MAX), @SplitOn VARCHAR(5))  
RETURNS @RtnValue TABLE 
(
    Id int identity(1,1),
    Data VARCHAR(MAX)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Once created, you can do the following to obtain your results:

SELECT *
FROM YourTable A
CROSS APPLY dbo.Split(Label,', ') B

By not adding the identity in the 'TABLE' result, the function executes much faster, and remember to add the SplitOn Parameter in the function. — Jaques, Mar 14 '12 at 13:24
@Jaques - Yeap, you are right, I updated my answer. About the identity, it does make the function slower, but I've found it really useful when the order of the output matters. — Lamak, Mar 14 '12 at 13:32

score 2 · Answer 2 · answered Mar 14 '12 at 12:47

Here, I have made Table Valued Function which splits the string and return the result as your desired

--Create the function
    CREATE FUNCTION dbo.Split(@ProjectId nvarchar(50),@String varchar(8000), @Delimiter char(1))       --Pass projectID,label and delimiter and returns table 
    returns @temptable TABLE (id nvarchar(50),items varchar(8000))       
    as       
    begin       
        declare @idx int       
        declare @slice varchar(8000)       

        select @idx = 1       
            if len(@String)<1 or @String is null  return       

        while @idx!= 0       
        begin       
            set @idx = charindex(@Delimiter,@String)       
            if @idx!=0       
                set @slice = left(@String,@idx - 1)       
            else       
                set @slice = @String       

            if(len(@slice)>0)  
                insert into @temptable(id,Items) values(@ProjectId,@slice)       

            set @String = right(@String,len(@String) - @idx)       
            if len(@String) = 0 break       
        end   
    return       
    end  
--Calling the function
select * from dbo.split('1200',' label1, label2, label3',',')  --calling teh function

score 0 · Answer 3 · answered Mar 15 '12 at 06:42

create table #comma_seprate
(ProductID int,
Lable varchar(max))

declare @index int, @id int;
declare @lable varchar(max);
declare cur_comma cursor
for select ProductID, Lable from comma_seprate
open cur_comma
fetch next from cur_comma into @id, @lable
while (@@fetch_status=0)
begin
    set @index=charindex(',',@lable);
    while(@index>0)
    begin
        insert into #comma_seprate values (@id,rtrim(ltrim(left(@lable,@index-1))));
        set @lable=substring(@lable,@index+1,len(@lable));
        set @index=charindex(',',@lable);
    end
    insert into #comma_seprate values (@id, rtrim(ltrim(@lable))); 
    fetch next from cur_comma into @id,@lable;
end
close cur_comma;
deallocate cur_comma;
select * from #comma_seprate;
truncate table #comma_seprate;

score -1 · Answer 4 · answered Mar 14 '12 at 12:32

-1

Use SQL Server table function with split function which returns a table

answered Mar 14 '12 at 12:32

Kishore Kumar

12,675
27
97
154

3

`PIVOT` won't help at all for this – Lamak Mar 14 '12 at 12:35

show one column in multiple rows

4 Answers4