asked on

SQL syntax

Hello,

I need to transpose the information in a table and substitue words for letter abreviations, basically taking sevral rows of data and putting it into one row/column.

Here is a sample of the information in my DB currently...

[Bird ID] [Defects]
-----------------------------------
365965544 Slight Legs
365965544 Toes
365965545 Slight Legs
365965545 Toes
365965549 Severe Legs
365965549 Severe Toes
365965551 Slight Legs
365965553 Slight Legs

Here is what i want to turn it into...

[Bird ID] [Defects]
-----------------------------------
365965544 SL, T
365965545 SL, T
365965549 SL, ST
365965551 SL
365965553 SL

Thanks!

-Navicerts

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Navicerts

ASKER

I think this will work great. I can add a case statement to replace the string with letter abbreviations, unless you know a better way?

Here is what I got it is working good for me, Thank You!! (I need to add the rest of the letter replacements in)

create function dbo.concate_itemid ( @birdid int )
returns varchar(8000)
as
begin
declare @res varchar(8000)
select @res = coalesce( @res + ',' , '') + case when defect = 'Slight Legs' then 'SL' else defect end
from Defects where [bird id]= @birdid
return (@res)
end

GO

select [bird id], dbo.concate_itemid([bird id])
from defects
group by [bird id]

-Navicerts

Guy Hengel [angelIII / a3]

I would, instead of doing it in the code, have a "translation" table of the values and the replacements...

you might also try to do it automatically, by extracting all the characters after a space...

create function dbo.GetShortValue ( @d varchar(100) )
returns varchar(100)
as
begin
declare @res varchar(100)
declare @x int

set @res = substring(@d, 1,1)
set @x = 2
while (@x > 0)
begin
set @x = charindex(@d, ' ' , @x)
if (@x>0)
begin
set @res = @res + substring(@d, @x+1, 1)
set @x = @x +2
end
end

return (@res)
end

and use it like this:

create function dbo.concate_itemid ( @birdid int )
returns varchar(8000)
as
begin
declare @res varchar(8000)
select @res = coalesce( @res + ',' , '') + dbo.GetShortValue (defect )
from Defects where [bird id]= @birdid
return (@res)
end