Columns to rows

Transact-SQL
1

Hi,

I am having the data like below,

ID TASK_NAME TASK_OWNER OWNER2

1 Task_one Miller John;Peter
2 Task_two Tom James;Laura;Denise

I am looking for the output like below,

ID TASK_NAME OWNER
1 Task_one Miller
1 Task_one John
1 Task_one Peter
2 Task_two Tom
2 Task_two James
2 Task_two Laura
2 Task_two Denise

I have modified the input and output....

Is there any chance this can be achieved through Query.

.

2 
declare @mytable table(ID int, TASK_NAME char(10), OWNER1 char(10), OWNER2 char(10), OWNER3 char(10))
insert into @mytable(ID, TASK_NAME, OWNER1, OWNER2, OWNER3) values
(1, 'Task_one','John','Peter','Miller'),
(2, 'Task_two','James','Laura','Denise')

select ID, TASK_NAME, task_owner
from @mytable
cross apply 
(
    values(owner1),(owner2), (owner3)
) _(task_owner)

order by ID, TASK_NAME, task_owner
RETURN;
3

@gbritton, what is the underscore before task_owner? I know this code works and that task_owner is the column name.

4

The underscore is the alias for the subquery. It's something I use when I don't care about the alias, just the column names. Comes from a paradigm I learned when I learned Python

5

Hi,

I have modified the input and output....

ID TASK_NAME TASK_OWNER OWNER2

1 Task_one Miller John;Peter
2 Task_two Tom James;Laura;Denise

I am looking for the output like below,

ID TASK_NAME OWNER
1 Task_one Miller
1 Task_one John
1 Task_one Peter
2 Task_two Tom
2 Task_two James
2 Task_two Laura
2 Task_two Denise

Is there any chance this can be achieved through Query.

6

sure,but you just made it harder. you'll need to read splitter and examples

7

Hi,

I have got the output with the below code,

WHILE (CHARINDEX(';', @OWNER2, 0) > 0)
BEGIN
SET @IntLocation = CHARINDEX(';', @OWNER2, 0)
INSERT INTO #TEMP_MAIL (TASKID,OWNER)
SELECT @Rec$id,RTRIM(LTRIM(SUBSTRING(@OWNER2, 0, @IntLocation)))
SET @OWNER2= STUFF(@OWNER2, 1, @IntLocation, '')
END

Thanks for your help.

8

That'll work, but won't scale well. That approach is a performance killer.

9

Hi,

Yes Its killing the performance. Is there anyother way can we achieve this functionality with good performance.

Thanks in advance.

10

Check out what I posted before regarding the splitter