Post reply

Pivot Columns and Concatenate Text

  • November 29, 2010 at 2:05 pm

    #227666

    I have a rather interesting requirement from a client. They have data in a format similar to the below example:

    declare @delivery table (

    CustomerNr int,

    MonDeliveryStartTime nchar(4),

    MonDeliveryEndTime nchar(4),

    TueDeliveryStartTime nchar(4),

    TueDeliveryEndTime nchar(4))

    insert into @delivery (CustomerNr, MonDeliveryStartTime, MonDeliveryEndTime, TueDeliveryStartTime, TueDeliveryEndTime)

    (

    SELECT 1, '0500', '1800', '0530', '0900'

    UNION

    SELECT 2, '1300', '1400', '0600', '0800'

    UNION

    SELECT 3, '1000', '1030', '1300', '1900'

    UNION

    SELECT 4, '0830', '1700', '0000', '2359'

    )

    select * from @delivery

    The final output should look like the example below:

    declare @finaldelivery table (

    CustomerNr int,

    DeliveryWindow nvarchar(4000)

    )

    INSERT INTO @finaldelivery (CustomerNr, DeliveryWindow)

    (

    SELECT 1, 'Monday 0500-1800, Tuesday 0530-0900'

    UNION

    SELECT 2, 'Monday 1300-1400, Tuesday 0600-0800'

    UNION

    SELECT 3, 'Monday 1000-1030, Tuesday 1300-1900'

    UNION

    SELECT 4, 'Monday 0830-1700, Tuesday 0000-2359'

    )

    select * from @finaldelivery

    I've tried using Stuff(()) FOR XML but haven't been able to make progress. Thank you for any help that you can provide!

  • November 29, 2010 at 3:04 pm

    #1256516

    Did you try this?

    SELECT CustomerNr,

    DeliveryWindow = 'Monday ' + MonDeliveryStartTime + '-' + MonDeliveryEndTime +

    ', Tuesday ' + TueDeliveryStartTime + '-' + TueDeliveryEndTime

    FROM @delivery

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 30, 2010 at 6:53 am

    #1256763

    Wow, I feel stupid. I was way overcomplicating things. Thanks for the help, Wayne!!

  • November 30, 2010 at 7:47 am

    #1256804

    No problem - glad I could help you out.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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