Need to bring this Output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to bring this Output

Author

Topic

urzsuresh
Starting Member

30 Posts

Posted - 2010-10-21 : 04:09:20

Hi friends,
Can anyone plz guide me through code


Declare @t Table
(
        id int,
        EmpName nvarchar(500),
        status char(5),
        Repair int
)

Insert Into @t
Select 1,'Arun,Balu','Yes',25 Union All
Select 2,'Nagu','No',20 Union All
Select 3,'Mani','No',45 Union All
Select 4,'XXX,YYY','No',5 
select * from @t

My Requried Output is Below


id  EmpName status  repair
1	Arun	Yes  	25
1	Balu	Yes  	25
2	Nagu	No   	20
3	Mani	No   	45
4	XXX  	No   	5
4	YYY	No   	5

Suri

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 04:31:25

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151883

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-21 : 06:01:36

First you need this function to create (only once):

CREATE FUNCTION [dbo].[fnParseArray] (@array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (occ int,parmValue varchar(50))
AS 
BEGIN
DECLARE @separator_position INT 
DECLARE @array_value VARCHAR(1000) 
declare @occurence int =1 

if (left(@array,1)=@separator)
begin
        set @array=stuff(@array,1,1,'')
end

if (right(@array,1)<>@separator)
begin
        SET @array = @array + @separator
end

WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT into @T VALUES (@occurence,@array_value) 
set @occurence = @occurence + 1
SELECT @array = STUFF(@array, 1, @separator_position, '')
END

RETURN 
END

Then you can do this:

Declare @t Table
(
        id int,
        EmpName nvarchar(500),
        status char(5),
        Repair int
)

Insert Into @t
Select 1,'Arun,Balu','Yes',25 Union All
Select 2,'Nagu','No',20 Union All
Select 3,'Mani','No',45 Union All
Select 4,'XXX,YYY','No',5 
--select * from @t


select
id,
parmValue as EmpName,
status,
Repair
from @t
cross apply dbo.fnParseArray(EmpName,',')

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources