Post reply

Concatenating Values from Different Rows

  • October 31, 2006 at 12:53 pm

    #121197

    I have a table which not only stores the date and time associated with an event in different varchar fields, but stores them in different rows as well. Here's the structure, simplified for this example:

    Table: ComponentInst

    EventNumber int

    Table: ComponentVal

    UniqueID bigint,

    EventNumber int,

    EventLabel varchar(20),

    EventData varchar(100),

    So the source data in ComponentVal looks like this:

    1 100 StartDate '5/1/2006'

    2 100 StartTime '4:00 PM'

    3 101 StartDate '7/5/1990'

    4 101 StartTime ''

    I'm trying to create rows that look like this:

    100 '5/1/2006 4:00:00 PM'

    101 '7/5/1990'

    and store them in a datetime column.

    I plan to write a function that validates the EventData values before concatenating them, but I don't know how to pass the two values in together at the same time when they're sitting in different rows.

    The select statement I've put together looks like this:

    SELECT

    ci.EventNumber,

    cv.EventLabel,

    cv.EventData

    FROM ComponentInst ci

    INNER JOIN ComponentVal cv

    ON ci.EventNumber = cv.EventNumber

    Thanks,

    Mattie

     

     

     

  • October 31, 2006 at 1:38 pm

    #669065

    DECLARE @ComponentInst TABLE(EventNumber int)

    INSERT INTO @ComponentInst

    SELECT 100 UNION ALL

    SELECT 101 UNION ALL

    SELECT 102 UNION ALL

    SELECT 103

    DECLARE @ComponentVal TABLE(

    UniqueID bigint,

    EventNumber int,

    EventLabel varchar(20),

    EventData varchar(100))

    INSERT INTO @ComponentVal

    SELECT 1, 100 ,'StartDate' ,'5/1/2006' UNION ALL

    SELECT 2, 100 ,'StartTime','4:00 PM' UNION ALL

    SELECT 3, 101 ,'StartDate', '7/5/1990' UNION ALL

    SELECT 4, 101 ,'StartTime', '' UNION ALL

    SELECT 5, 102 ,'StartDate', '7/5/1990'

    /*

    --desired result

    100 '5/1/2006 4:00:00 PM'

    101 '7/5/1990'

    */

    SELECT ci.EventNumber,cv.EventLabel,cv.EventData

    FROM @ComponentInst ci INNER JOIN @ComponentVal cv

    ON ci.EventNumber = cv.EventNumber

    SET DATEFORMAT DMY

    SELECT cv1.EventNumber as EvNo,

    CAST(cv1.EventData+' '+ISNULL(cv2.EventData,'') as DateTime) as EvData

    FROM @ComponentInst ci INNER JOIN @ComponentVal cv1

    ON ci.EventNumber = cv1.EventNumber

    LEFT OUTER JOIN @ComponentVal cv2

    ON cv1.EventNumber = cv2.EventNumber

    WHERE cv1.EventLabel='StartDate' and cv2.EventLabel='StartTime'

     

    Kindest Regards,

    Vasc

  • October 31, 2006 at 1:42 pm

    #669067

    I'm afraid to ask but here it goes :

    Why isn't this data kept in a single date time column?

    In a single ROW????

  • October 31, 2006 at 2:43 pm

    #669084

    Agreed, how about a little meaning to this problem. I mean the record with a 'StartTime' label and no start time really has me worried.

  • November 1, 2006 at 5:50 am

    #669176

    This should get your juices flowing. Have your pick from the following three. I'll leave it up to you to make them robust. Thus the first one assume there are always both StartTime and EndTime present. The other two don't care. Etc. Etc.

    select d.EventNumber,convert(datetime,d.EventData+' '+t.EventData)

    from

      (select * from events where EventLabel='StartDate') d

      join

      (select * from events where EventLabel='StartTime') t on d.EventNumber=t.EventNumber

    select EventNumber,

      max(case when EventLabel='StartDate' then EventData else '' end) d,

      max(case when EventLabel='StartTime' then EventData else '' end) t

    from events

    group by EventNumber

    select EventNumber,

      max(case when EventLabel='StartDate' then EventData else '' end)+' '+max(case when EventLabel='StartTime' then EventData else '' end) dt

    from events

    group by EventNumber

  • November 1, 2006 at 5:54 am

    #669177

    #4

    Select

     CV1.EventNumber,

     isnull(CV1.EventData + ' ','')  +  isnull(CV2.EventData,'') EventDateTimeString

    from

     ComponentInst CI,

     ComponentVal CV1,

     ComponentVal CV2

    where

     CI.EventNumber = CV1.EventNumber

     and CV1.EventNumber = CV2.EventNumber

     and CV1.EventLabel = 'StartDate'

     and CV2.EventLabel = 'StartTime'

  • November 1, 2006 at 6:03 am

    #669179

    Good use of SelfJoin....Welcome David...

  • November 1, 2006 at 6:11 am

    #669180

    I try to stick with Join, Left Join, Right Join, Full Join and Cross Join and try to keep the reduntant and confusing OUTER out. But regarding pre SQL92 syntax, what's the difference between a self-join and an equi-join? This later term was used by someone in a recent response to one of my posts.

  • November 1, 2006 at 8:02 am

    #669214

    Hi Michael,

    A self-join is where a table is joined to itself for recursive or higherarchial operations (such as concatenating values from different rows).

    An equi-join is basically where the join operator is looking for rows that are equal (e,g, JOIN table1 ON table.id = Table1.t1ID)

    Just to confuse matters more, there are also Left and right semi and anti-semi joins if you are interested, I would check out Craig Freedman's Blog:

    http://blogs.msdn.com/craigfr/archive/2006/07/19/671712.aspx

    SQL guy and Houston Magician

  • November 1, 2006 at 8:29 am

    #669224

    Thanks to everyone who responded. The 'max' path suggested by Mike was the one that occurred to me before I posted, but the fog of derived tables descended (to mix metaphors), and I became hopelessly lost.

    I ended up using Vasc and David's solutions, primarily because joins of actual tables are much more intuitive to me than joins of derived tables. Here's what I ended up with:

    SELECT
    ci.EventNumber,
    cast (cv1.FieldValue + ' ' + cv2.FieldValue as datetime) as testdatetime
     
    FROM ComponentInst ci
    INNER JOIN ComponentVal cv1
    ON ci.EventNumber = cv1.EventNumber
    INNER JOIN ComponentVal cv2
    ON ci.EventNumber = cv2.EventNumber
     
    WHERE cv1.FieldName = 'StartDate'
    AND cv2.FieldName = 'StartTime'

    The Inner Join on cv2 will work because there is always a row for 'StartDate' and 'StartTime'.

    Now, as to Remi's question as to why these values are not in the same row and column. If I were designing the database, they would be. That said, this database is supplied by a vendor to support their application, and on the whole, it is a wonderfully designed, very intuitive, very normalized database. Unfortunately, to customize pieces of their application for the remarkably diverse conditions encountered in different judicial jurisdictions, they have had to resort to the completely denormalized structure of 'field/value' pairs. While that doesn't explain why the date and time aren't stored as one piece, it does explain the 'column as row' structure, which I thought would be greeted by a lot more outrage.

    Thanks again.

    Mattie

     

  • November 1, 2006 at 8:45 am

    #669227

    Thanx for clearing that up.  As for the outrage part, I can't say I didn't feel the need to but I try to let the user explain the reasons before I educate.  Glad to see you don't need my help on that matter .

     

    Good luck defogging the rest of that project .

Viewing 11 posts - 1 through 10 (of 10 total)

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