July 16, 2008 at 12:37 am
I am having data in a table like this:
ID Name Value Posn
HOXE EMAIL g.mang@tjh.com 30
HOXE EMAIL g1.mang1@tjh.com 31
HOXE FAX 91-821-2660585 20
HOXE PHONE 91-821-2660420 10
KTSA EMAIL ga.jais@tjh.com 30
KTSA FAX 91-2990-150444 20
KTSA PHONE 91-2990-151874 10
KTSA PHONE 91-2990-152638 11
LTSA EMAIL la.lais@tjh.com 30
LTSA FAX 91-2997-550444 20
LTSA FAX 91-2997-550444 21
LTSA PHONE 91-2997-551874 10
I want output with 4 columns like this:
ID Phone Fax Email
HOXE 91-821-2660420 91-821-2660585 g.mang@tjh.com, g1.mang1@tjh.com
KTSA 91-2990-151874, 91-2990-152638 91-2990-150444 ga.jais@tjh.com
LTSA 91-2997-551874 91-2997-550444, 91-2997-550444 la.lais@tjh.com
If more than 1 phone or email or fax is available then it should be concated with comma seperation.
Can you Please Help?
July 16, 2008 at 3:08 am
Hi
Here is a quick and dirty way of doing it. Im sure there is more elegant ways - but this was all I had time for.
DECLARE @id Varchar(30),
@Value Varchar(30),
@string Varchar(255)
create table #tempt
(id varchar(30),
string varchar(255))
DECLARE Test CURSOR FOR
SELECT DISTINCT ID
FROM test99
ORDER BY ID
OPEN Test
FETCH NEXT FROM Test
INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string = ''
DECLARE Test2 CURSOR FOR
SELECT value
FROM test99
WHERE ID = @id
ORDER BY NAME
OPEN Test2
FETCH NEXT FROM Test2
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @String = @string + @Value +','
FETCH NEXT FROM Test2
INTO @Value
END
CLOSE Test2
DEALLOCATE Test2
SET @String = STUFF(reverse(@String), 1, 1,'')
INSERT INTO #tempt
VALUES(@id, @string)
FETCH NEXT FROM Test
INTO @Id
END
SELECT * FROM #tempt
DROP TABLE #tempt
CLOSE Test
DEALLOCATE Test
Telammica
July 16, 2008 at 3:14 am
PS. This was my sample data
create table test99
(id varchar(30), name varchar(40), value varchar(40), posn int)
insert into test99 values ('HOXE', 'EMAIL', 'asdf@asdf', 30)
insert into test99 values ('HOXE', 'FAX', '9999', 11)
insert into test99 values ('HOXE', 'PHONE', '888', 12)
insert into test99 values ('HOXE', 'EMAIL', 'asdf@asdf', 30)
insert into test99 values ('KTSA', 'EMAIL', 'asdf@asdf', 44)
insert into test99 values ('KTSA', 'FAX', '11188', 50)
insert into test99 values ('KTSA', 'PHONE', '111111', 30)
insert into test99 values ('KTSA', 'EMAIL', '111@222', 30)
insert into test99 values ('LTSA', 'EMAIL', '111@222', 30)
insert into test99 values ('LTSA', 'EMAIL', 'asdf@asdf', 90)
insert into test99 values ('LTSA', 'FAX', '111@222', 24)
insert into test99 values ('LTSA', 'PHONE', '88888', 90)
July 16, 2008 at 5:24 am
Here is a much better solution.
CREATE FUNCTION dbo.fnGetValue
(
@id VarChar(30)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return,'') + Value + ','
FROM test99
WHERE id = @id
ORDER BYname desc
SET@return = reverse(STUFF(reverse(@return), 1, 1,''))
RETURN @Return
END
SELECT DISTINCT ID,
dbo.fnGetValue(id) As Value
FROM test99
ORDER BY ID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply