SQLTeam.com | Weblogs | Forums

Columns to rows


#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