Post reply

Is there a way to do this?

  • October 8, 2007 at 2:04 pm

    #175798

    Using sql Server 2000. I have a details table, a lookup table and an intersection table. Each detail row can have 1 or more values assigned to it from the lookup table. The intersection holds the IDs from the details table and the lookup table. What I'm trying to do, is for each row in the details table, put into a single field, all the text values from the lookup table. I can join together and get the information but I have all the detail rows data repeated.

    select datavalue

    from detail idt

    inner join (

    select ityp.datavalue, itr.Issue_id

    from intersection itr

    inner join lookup ityp on ityp.typeID = itr.type_id) tblTypes on tblTypes.issue_ID = idt.issueID

    Detail table

    IssueID

    Job

    Description

    InterSection table

    Issue_ID

    Type_ID

    Lookup Table

    TypeID

    DataValue

    Sample Data

    Detail Table

    1 12345 Test1

    2 54321 Test2

    3 98765 Test3

    Intersection Table

    1 1

    1 2

    1 3

    2 3

    2 4

    3 1

    3 3

    3 4

    Lookup Table

    1 Blistered

    2 Creased

    3 Brittle

    4 Discolored

    This is the results I’d like to get ( a bonus would be a comma in between each)

    ID Job Description Issues

    1 12345 Test1 Blistered, Creased, Brittle

    2 54321 Test2 Brittle, Discolored

    3 98765 Test3 Blistered, Brittle, Discolored

    Thanks

  • October 8, 2007 at 2:44 pm

    #740156

    Are there only four values in the look up table or are there more? Will there be more in the future?

  • October 8, 2007 at 2:45 pm

    #740157

    There are more, could be lots more over time.

    Allen

    Thanks

  • October 8, 2007 at 2:58 pm

    #740164

    This should probably give you what you want (didn't catch the specifics on all field names, so you will have to play find and replace). This is a shameless adaptation from another prolific poster's technique using numbers, but it should work.

    The other way would be to use a user-defined function to calculate the string - but I think this will actually be faster.

    Tables Detail (ID, ), Lookup (lookupID matching to detail, issueid matching to issue), issues (your reference table issueID, issuedesc)

    Now - since we are using a field to capture this - this will be prone to overflow length-wise if there are too many strings to concatenate.

    My ID's were string - make your match the type you need.

    --working table required

    create table #temp (lookupid varchar(10),issuedesc varchar(10),tmp varchar(4000))

    --insert the stuff you need to flatten

    insert #temp (lookupid ,issuedesc,tmp)

    select l.lookupid, i.issuedesc from lookup l inner join issues i on l.issueid=i.issueid

    --make this run a little faster

    create index ix_tmp on #temp(lookupid)

    --and now - for the fun part

    declare @holder varchar(4000)

    declare @previd varchar(10)

    declare @g datetime

    set @g=getdate()

    set @holder=''

    set @previd=''

    update #temp

    set @holder=tmp=@holder+issuedesc, @holder =case when @previd=lookupid then @holder+',' else '' end, @previd=lookupid

    from #temp with (index(ix_tmp),tablock)

    select d.*, i.issuestr

    from details d inner join

    (select lookupid, max(tmp) as issuestr from #temp group by acctnum) i on d.id=i.lookupid

    drop table #temp

    select datediff(ms,@g,getdate())

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 8, 2007 at 3:03 pm

    #740167

    Thanks, I'll give that a shot.

    Allen

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply